Under what conditions are polymorphic associations used? What are the alternatives?

Question

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?

Answer 1

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.....