This is wrong: a column can references primary keys in different tables depends on value of another column?

Question

One of my colleague designed a table schema, and in one of the tables a column can references primary keys in different tables depends on value of another column. I know it is differently wrong, but can not find theory to support me. His schema like this:

table video: (id, name, ...)
table audio:(id, name, ...)
table review_item( item_type, item_id, reason, ...)

when item_type='V', then item_id is id of table video and when item_type='A' then item_id is id in table video

The combination of (item_type, item_id) is unique, but actually it is not a foreign key at all, you can not define it as foreign key as it does not point to a single table. DDL syntax does not allow it.

Can someone find out which principle or rules are violated here?

Answer 1

Clearly, it's wrong. You may claim it violates the 'Single Responsibility Principal'; i.e. a thing should have a specific, clear purpose. But be clear, with examples, as to how this is bad (don't just state the term/phrase and expect it to be good enough).

The main problem I'd have with it, is that I don't think a DB would be able to enforce the foreign key constraint on such a basis (maybe it can via some custom rule). I.e. in MS SQL you couldn't reference the other tables primary key and have it enforce it, and given that integrity - i.e. the enforcement of constraints - is one of the main benefits of FKs, this would be my main argument. Depending on how your OR/Mapper operates (if you use one) I'd bring this up as well. It may not support the strategy he has taken.

Answer 2

It'll work but it points to other possible mistakes. I could think of worse designs. Enforcing integrity would be rought.

You could join of course but need an extra condition in the 'where' to constrain by item_type.

This type of arrangment probably indicates that the audio and video tables ought to be one table with other columns distinguishing the audio/videoness, and 1-1 links to audio-only or video-only info if you get into that kind of jam. But it seems like a lot of the same info - in the real world - applies to both. In fact would not a video table have to have all the audio info anyway unless it were a silent movie :-) ??? If only one applies, put in nulls.

This would be easy if you could use an oo-db, right? j/k...

Answer 3

See this example of how you can enforce foreign keys in this situation: http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

The item_type column violates 2nd Normal Form, BUT I think that's OK because: A) You can easily implement CHECK constraints which mean anomalies cannot arise, B) it's a small price to pay in order to enforce the very useful constraint that an item cannot be of more than one type. Most SQL DBMSs cannot enforce that rule declaratively unless you push the type column into each table.