Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As I understand it, polymorphic relationships among tables in a database allow one table to have one foreign key that references many tables.

I have found that tags are a common application of this structure. Many tables may have a tag, and a polymorphic relationship allows each of these tables to reference a single tags table.

Unfortunately, I have not been able to find very much background on this topic, either on google or on this site. Most of the writing is either too general (c.s. theory) or too specific (implementation).

I would like to learn more about their use, and how these structures compare to alternatives. Is there a good reference on the use of polymorphic associations in databases?

share|improve this question
1  
This annswer may be of help: Why can you not have a foreign key in a polymorphic association? –  ypercube Jul 12 '12 at 13:13
add comment

1 Answer

In general, I have never found polymorphic associations to work very well. The problem is that once you open up something like this, you have to have unique identifiers across the tables and these need to be enforced. Otherwise you end up with a case where "customer has the same 'id' as an invoice and therefore ship-to information is mixed up."

The alternative is to define polymorphic association groups and use foreign keys. For example we might have a table entitled "objects" and in it we might have two columns: table_name and object_id. We can then use triggers to maintain that table and have all tables in the polymorphic set define foreign keys against this association catalog (by table name and id) and we can therefore guarantee uniqueness that way. We can then set foreign keys on the other side, and reference the catalog table. This isn't perfect If there is a trigger malfunction it is possible for a reference to be dangling, but it avoids the worst of the join projection and insert anomilies. Also depending on the complexity of the catalog, mutual foreign keys may be useful.

Finally some databases (like PostgreSQL) would allow table inheritance to be used to create something like this, but this is a rather advanced topic.....

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.