Database Design – Conditionally Join Tables with Foreign Key

conditiondatabase-designforeign keyjoin;

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me.

I have a master table which is then joined to another table but the table that is joined is based on some data in another column.

Example ER Diagram

My application will in turn run queries such as:

SELECT f.dataA, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foo f ON f.id = m.TableKey
WHERE TableName = 'Foo'

and

SELECT fb.dataA, fb.dataB, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foobar fb ON fb.id = m.TableKey
WHERE TableName = 'Foobar'

This works as desired but I don't like the fact that my Master_Table.TableKey should probably be held to a foreign key constraint but this is impossible with this design (or at least I think so).

Should the TableKey column be split out to being a FooKey, BarKey and FoobarKey? This also then seems odd because I then guarantee that BarKey and FoobarKey will be null where TableName = "Foo".

Or is there a completely different way I should be approaching this?

EDIT: Funnily enough this recent meta.dba post seems to touch on this idea about inheritance but I'm not too sure what the best approach still is.

Best Answer

This is not a great design. You have a couple of options to improve it:

  • Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints.

  • Put the parent key in the child table. If every child record refers to one master record, just put the masterid in the child table. This also gives you the option of multiple child records per master/parent. If you want to have only one child per parent, remove the surrogate key in the child table and just have the related master id.

Whatever you choose, I would recommend against storing the child table name in a column in the master record. You can make this a "Type" field, but putting meta data into your data always leads to pain down the road. It's also unnecessary if you have separate keys, as you know if you have a FooID value the record type is Foo.

Related Question