多态关联外键约束。这是一个好的解决方案吗?

发布于 2024-12-12 13:55:35 字数 691 浏览 0 评论 0 原文

我们在应用程序中使用多态关联。我们遇到了经典问题:我们遇到了无效的外键引用,并且无法创建外键约束,因为它是多态关联。

也就是说,我对此做了很多研究。我知道使用多态关联的缺点和优点。但我发现似乎是一个不错的解决方案:

http://blog.meta Mind.com/2010/11/25/stable-polymorphic-foreign-key-relations-in-rails-with-postgresql/

这是很好,因为你可以两全其美。我担心的是数据重复。我对 postgresql 的了解不够深入,无法完全理解这个解决方案的成本。

你有什么想法?应该完全避免这种解决方案吗?或者这是一个好的解决方案?

在我看来,唯一的选择是为每个关联类型创建一个外键。但随后您会验证是否只存在一个关联。这是一个“选择你的毒药”的情况。多态关联清楚地描述了意图,也使这种情况变得不可能。我认为这是最重要的。数据库外键约束是一个幕后功能,改变“意图”以适应数据库限制对我来说是错误的。这就是为什么我想使用上述解决方案,假设没有明显的“避免”。

We're using polymorphic associations in our application. We've run into the classic problem: we encountered an invalid foreign key reference, and we can't create a foreign key constraint, because its a polymorphic association.

That said, I've done a lot of research on this. I know the downsides of using polymorphic associations, and the upsides. But I found what seems to be a decent solution:

http://blog.metaminded.com/2010/11/25/stable-polymorphic-foreign-key-relations-in-rails-with-postgresql/

This is nice, because you get the best of both worlds. My concern is the data duplication. I don't have a deep enough knowledge of postgresql to completely understand the cost of this solution.

What are your thoughts? Should this solution be completely avoided? Or is it a good solution?

The only alternative, in my opinion, is to create a foreign key for each association type. But then you run into validating that only one association exists. It's a "pick your poison" situation. Polymorphic associations clearly describe intent, and also make this scenario impossible. In my opinion that is the most important. The database foreign key constraint is a behind the scenes feature, and altering "intent" to work with database limitations feels wrong to me. This is why I'd like to use the above solution, assuming there is not a glaring "avoid" with it.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

遮云壑 2024-12-19 13:55:36

我对 PostgreSQL 的 INHERITS 实现遇到的最大问题是无法设置对父表的外键引用。在很多情况下您都需要这样做。请参阅我的答案末尾的示例。

在 Rails 之外创建表、视图或触发器的决定是至关重要的。一旦你决定这样做,那么我认为你最好使用你能找到的最好的结构。

我长期以来一直使用基本父表,使用外键强制执行不相交的子类型。此结构保证只能存在一个关联,并且该关联解析为父表中的正确子类型。 (在 Bill Karwin 关于 SQL 反模式的幻灯片中,这种方法从幻灯片开始46.) 在简单的情况下,这不需要触发器,但我通常为每个子类型提供一个可更新的视图,并且需要客户端代码来使用这些视图。在 PostgreSQL 中,可更新视图需要编写触发器或规则。 (9.1 之前的版本需要规则。)

在最一般的情况下,不相交的子类型不具有相同数量或类型的属性。这就是为什么我喜欢可更新的视图。

表继承是不可移植的,但这种结构是可移植的。您甚至可以在 MySQL 中实现它。在 MySQL 中,您必须使用单行表的外键引用来替换 CHECK 约束。 (MySQL 解析并忽略 CHECK 约束。)

我认为您不必担心数据重复。首先,我非常确定父表和继承表之间的数据不会重复。看起来就是这样。其次,完整性完全由数据库管理系统控制的复制派生数据并不是特别难以下咽的苦药丸。 (但是不受控制的重复是。)

考虑一下删除是否应该级联。

The biggest problem I have with PostgreSQL's INHERITS implementation is that you can't set a foreign key reference to the parent table. There are a lot of cases where you need to do that. See the examples at the end of my answer.

The decision to create tables, views, or triggers outside of Rails is the crucial one. Once you decide to do that, then I think you might as well use the very best structure you can find.

I have long used a base parent table, enforcing disjoint subtypes using foreign keys. This structure guarantees only one association can exist, and that the association resolves to the right subtype in the parent table. (In Bill Karwin's slideshow on SQL antipatterns, this approach starts on slide 46.) This doesn't require triggers in the simple cases, but I usually provide one updatable view per subtype, and require client code to use the views. In PostgreSQL, updatable views require writing either triggers or rules. (Versions before 9.1 require rules.)

In the most general case, the disjoint subtypes don't have the same number or kind of attributes. That's why I like updatable views.

Table inheritance isn't portable, but this kind of structure is. You can even implement it in MySQL. In MySQL, you have to replace the CHECK constraints with foreign key references to one-row tables. (MySQL parses and ignores CHECK constraints.)

I don't think you have to worry about data duplication. In the first place, I'm pretty sure data isn't duplicated between parent tables and inheriting tables. It just appears that way. In the second place, duplication or derived data whose integrity is completely controlled by the dbms is not an especially bitter pill to swallow. (But uncontrolled duplication is.)

Give some thought to whether deletes should cascade.

安静被遗忘 2024-12-19 13:55:36

您无法以简单的方式在数据库中强制执行此操作 - 所以这是一个非常糟糕的主意。最好的解决方案通常是简单的 - 忘记多态关联 - 这是反模式的味道。

You cannot enforce that in a database in an easy way - so this is a really bad idea. The best solution is usually the simple one - forget about the polymorphic associations - this is a taste of an antipattern.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文