Why are first class polymorphic relations not possible in relational databases?

Question

Lets say I want to setup a roles table that has a polymorphic relationship to resource.

I understand that that I could directly setup a foreign key - by adding for example a roles.forum_id column.

But why is (AFAIK) creating a compound foreign key where one column holds the key and another the table to reference not possible?

Answer 1

A foreign key column references a single table. If you want to reference a different table as a foreign key, you have to have a different or second foreign key column. You can't have a single column that is a union type of different foreign keys (the first column of your suggested compound key). There is also no column type for table itself (the second part of your suggested compound key).

You can fake this by using a strings for keys and strings for tables (yuk!), but it will impact all of your tables and their queries rather dramatically.

---

All of this is part of what's known as the Object-Relational Mismatch .

Polymorphism by class is simply not a feature of the standard relational model, though some specific databases feature inheritance as an extension.

Support for polymorphism and/or inheritance is primarily featured in relational model as attributes: (1) attributes can be optional, which is they can store null instead of a value, and (2) new attributes can be added at will to support new subclasses. So, probably the cleanest map to a class with subclasses is to model every attribute in the same, single table, with attributes for subclasses as optional attributes, while attributes for the base class are required (if so).

This works pretty well for some stuff, but is not perfect in at least the following:

1. there is no special identification of what true subclass a particular row is from, and,
2. there is no way to ensure that the required attributes of a particular subclass are all present when the row is intended as an instance of that particular subclass (because we had to mark the subclass's attributes as optional).

For more information on modeling inheritance in relational db's, see:

• An illustration of three different ways, each of which have some drawback like I mention above: http://www.vertabelo.com/blog/technical-articles/inheritance-in-a-relational-database

• Another approach, Microsoft's Nordic Model

Answer 2

It is perfectly possible to model polymorphic data in a relational database. E.g. a series of nullable foreign keys to each resource type table, and a constraint that exactly one is non-null.