I have seen similar posts and have not found a definitive answer.
I have a series of tables that store data about certain events. Each of these tables have the same structure. Each of these tables has a foreign key constraint for an id showing what item the data is related to.
Each of these tables id structure is also the same CHAR(24). The tables these ids come from must remain separate because they are all completely different.
I am interested in combining all of my data tables into one with a foreign key being constrained to one of 3 tables. So, a row in my data table will have to have an id that is present in one of the three tables. Additionally this foreign key will need the possibility of ON DELETE settings. Is this possible? And to that, is this poor design?
Items A
- id
- ...
Items B
- id
- ...
Items C
- id
- ...
Data
- id FK
No. What you're describing is sometimes called polymorphic-associations but it should be a clue that it's not good design because you can't make a foreign key constraint for it. That is, the FOREIGN KEY syntax only allows you to reference one table, not three.
The only way you could make a real foreign key constraint that performs ON DELETE actions is to make three separate foreign keys:
Data
- idA FK
- idB FK
- idC FK
For a given row in Data, presumably only one of these three foreign keys would be non-NULL. The other two would be NULL. Ensuring this could be done in a trigger or CHECK constraint, or else you would just have to implement it in application logic (i.e. don't insert a row with more than one of these columns non-NULL).
Polymorphic associations, that is storing a single column that may reference one of three different tables, is not a valid relational design.
You can see past answers I've written about polymorphic associations: https://stackoverflow.com/search?q=%5Bpolymorphic-associations%5D+user%3A20860