15

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.

Jordan Brough
  • 6,808
  • 3
  • 31
  • 31
Binary Logic
  • 2,562
  • 7
  • 31
  • 39
  • 1
    the metaminded link is bad now, sadly - I was hoping to set up something like they described there – subelsky May 15 '14 at 17:58
  • ahh found lots of good stuff in mike sherrill's answer, and also in this other question: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association – subelsky May 15 '14 at 18:09

2 Answers2

10

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.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks for this. It sounds like this solution is not a bad solution. Obviously stepping outside of rails to do this is something I'd like to avoid, but I've come to find that as an application grows this is inevitable. As much as I'd like to rely on validates_uniqueness_of and :dependent => :destroy, without fail I will eventually find data integrity issues. You comments on data duplication were helpful, I was not aware PG did not actually duplicate the data. That was my main concern. – Binary Logic Oct 30 '11 at 22:27
3

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.

Szymon Lipiński
  • 27,098
  • 17
  • 75
  • 77
  • Actually, as part of my applications migrations, I can make implementing this solution very easy: a single method call. I'm not worried about implementing this as much as I am in how it effects my application once implemented. – Binary Logic Oct 30 '11 at 19:31
  • Here is the nice post about this anti-pattern: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association – Dmitry Polushkin Feb 27 '15 at 12:50