Is it a good idea to use a foreign key to reference 2 or more tables?

Question

Hi, Ruby on Rails ORM(object relational mapping) has a thing call polymorphic associations that allow a foreign key to be used to reference 2 or more other tables. This is achieved by creating an additional column called "type" that specifies the table with which the foreign key is associated with.

Does this implementation have a name from a database point of view? and is it good/bad practice?

thanks

Answer 1

Yes, using multiple keys to reference a unique record is known as a composite key. Whether it's good or bad practice is dependant on your database schema.

Example Scenario

Let's pretend that we have 4 tables: A, B, C and Z. Z maintains a reference to A, B, and C. Each record contains a reference to a single table. Below is two potential schema's for Z.

Single Foreign Key

We need a column to store the reference for each of the tables. That means we'll end up with NULL values for the unused columns. In future, if we introduce a D table, then we'll be required to add a new column to Z.

id | a_id | b_id | c_id
-----------------------
1  | 1    | NULL | NULL
2  | NULL | 1    | NULL
3  | NULL | NULL | 1

Composite Foreign Key

We start off with two columns for building a reference to the other tables. However, when we introduce D we do not need to modify the schema. In addition, we'll never have columns with NULL values.

id | z_id | z_type
------------------
1  | 1    | 'A'
2  | 1    | 'B'
3  | 1    | 'C'

Therefore, we can achieve some level of normalisation by using composite foreign keys. Provided that both columns are indexed, querying should be very fast. While it must be slower than using a single foreign key, the difference is insignificant.

Often it's tempting to use Rails' polymorphic associations whenever you have data that appears to be the same (Eg: Address). You should always exercise caution when coupling many models together. A good indicator you've gone too far is when you notice yourself switching based on the association type. A potential solution is to refactor common code out into a module and mix that into the models you care about instead.

Answer 2

Not all databases allow a composite foreign key and personally I'd shoot anyone who tried to do that to my database. Foreign keys MUST be maintained by the datbase not somethign like Rails. There are other processes which typically hit a database where this critical relationship must be checked which may not use an ORM (I certainly wouldn't use such a thing to import a 10,000, 000 record file or update a million price records or fix a data integrity problem.