Foreign-Keys for multiple tables

Question

i'm refactoring a db structure and have a little problem. This DB have various tables with same structure, like:

People -> People_contacts

Activities -> Activities_contacts

Now, i want to create only one Contact table, and use an ENUM() to distinguish from the nature of the parent (for search requirements and data reversibility)

the structure will be:

People -> Contacts[People]

Activities -> Contacts[Activities]

But now i need to put a Foreign-key, and based on the ENUM property distinguish from two different tables... How i can effort this? There are a way or is better maintain the old tables?

Answer 1

why you are using view? if the People_contacts and Activities_contacts are exactly the same, you can try this:

create view `test` as select *,'People' as Type from `People_contacts` union select *,'Activities' from `Activities _contacts` union;

and then select what you want from the view:

select * from `test` where Type = 'People' and .....

and your query answer should be this

+----+------+   +--------+
| ID | Data |...| Type   |
+----+------+   +--------+
| 1  | foo  |...| People |
| 2  | foo  |...| People |
+----+------+   +--------+