Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
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?
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:
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
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.
