This is wrong: a column can references primary keys in different tables depends on 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

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

Answer 1

You can do it as long as you don't try to specify a foreign key constraint on the item_id column. But then you are sacrificing integrity checks. You can't write a foreign key constraint (at least, in any version of SQL I have worked with) that would handle this.



You could write a trigger for review_item that checks the constraint on insert or update and throws an exception if invalid.



I would be tempted to add 2 more tables:

video_review:(video_id,review_id)

audio_review:(audio_id,review_id)



video_id is foreign key to video.id

audio_id is foreign key to audio.id

review_id is foreign key to review_item.id (which you didn't list...it should have an id column).



That does make it harder to add new types, since you need to create 2 tables instead of 1. It would probably simplify some joins, and make others more complex. But I think the same is true of the original.



Interesting question.

Answer 2

"Web Programmer" person is right.



But I think you, the asker, mean to say Foreign Key.



So,

There is a foreign key constraint on the table review_item, on column item_id referencing table video's id column.



Foreign key just makes sure that distinct values of the column item_id (of table review_item) exist in table video as well.

Example:

review_item:

'V' 1 reason

'V' 2 reason

'A' 3 reason

'V' 4 reason

'A' 5 reason

'V' 7 reason <- wrong, video doesn't have id 7.



video:

1 some_name1

2 some_name2

3 some_name3

4 some_name4

5 some_name5

6 some_name6 <- this is ok.



Video table can have more rows, as it's the parent table.



I hope this is what you wanted to know.

Answer 3

I have used this logic in the past.



This would be a unique key - a combination of item_id and item_type



I would still put an auto increment integer primary key on review_item call review_item_id