Conditional Foreign Key to multiple tables

Question

I have a table which contains two type of data, either for Company or Employee.

Identifying that data by either 'C' or 'E' & a column storing primary key of it.

So how can I give foreign key depending on data contained & maintain referential integrity dynamically.

id | referenceid  | documenttype 
-------------------------------
1  | 12           | E 
2  | 7            | C 

Now row with id 1 should reference Employee table with pk 12 & row with id 2 should reference Company table with pk 7.

Otherwise I have to make two different tables for both. Is there any other way to accomplish it.

Answer 1

Personally, i would go with the two different table option.

• Employee / Company seem to be distinct enough for me not to want to store their data together. That will make the foreign key references also straight forward.

However, if you do want to still store it in one table, one way of maintaining the referential integrity would be through a trigger.

• Have an Insert / Update trigger that checks the appropriate value in Company Master / Employee master depending on the value of column containing 'C' / 'E'

Personally, i would prefer avoiding such logic as triggers are notoriously hard to debug.

Answer 2

If you really want to do this, you can have two nullable columns one for CompanyId and one for EmployeeId that act as foreign keys.

But I would rather you to try and review the database schema design.

Answer 3

It would be better to normalize the table - Creating separate tables for Company and Employee. You would also get better performance after normalization. Sincec the Company and Employee are separate entities, its better not to overlap them.