0

Let's say I have the following tables:

table_a

id | col_1 | col_2 | col_3

In table_a, id is the primary key

table_b

id | col_1 | col_2 | col_3

In table_b, id is the primary key

table_c

id | col_1 | col_2 | col_3 | entity_id | entity_table

In table_c, id is the primary key, entity_id is the foreign key to either table_a or table_b and entity_table can take either table_a or table_b.

Now let's extrapolate this and say we have 10 different tables which entity_id can have foreign keys to.

What I'm trying to do is have the entity_id link to the specific table based on the entity_table column, which gives the table name.

I've done some research and it seems that we cannot really. have multiple foreign keys on a single column to multiple tables. How can I go about this and what would a good solution look like?

philipxy
  • 14,867
  • 6
  • 39
  • 83
nTuply
  • 1,364
  • 19
  • 42
  • Would the structure of the 2 (or 10?) A, B tables always be the same? Or could that vary? – Tim Biegeleisen Jan 03 '22 at 15:56
  • @TimBiegeleisen They could vary across the tables. The goal is to have a flexible table_a that can be used with multiple tables. The easiest way I thought of is to just skip the foreign key but I'm interested to know what would a solution involving foreign keys look like – nTuply Jan 03 '22 at 15:59
  • 1
    I'm marking this a duplicate of a question I answered almost 13 years ago! This is what I love about SQL: its mathematical foundations do not go out of style. You may also like [another answer](https://stackoverflow.com/a/922341/20860) I posted on this topic. – Bill Karwin Jan 03 '22 at 19:04
  • 1
    @BillKarwin There is an ordinary solution to this Q in `relational` & `sql`, with full integrity within the db, which was requested. Your referenced Q&A takes the mindset of `polymorphic-associations`, which is **not** Relational; **not** SQL. Further, it requires additional code in the framework (outside the db) to maintain integrity . Which is again, sub-standard relative to `relational` & `sql`. Your forcing this Q into something that is not, and then supplying a sub-standard A (not SQL) is incorrect. Please re-open the Q to allow what the seeker requested. – PerformanceDBA Jan 03 '22 at 20:52
  • 1
    @PerformanceDBA - Feel free to vote to reopen if you must, but I do believe the question is a duplicate. The various solutions I provided in my two linked answers do support referential integrity. – Bill Karwin Jan 03 '22 at 21:40
  • 1
    @BillKarwin  a) You have done the same thing in those A's (force the Q into something that it is not, and then supply a sub-standard A). So you have forced that apple_1 into an orange_1, forced this apple_2 into an orange_2, and then proposed that orange_2 is a duplicate of orange_1. That is three discrete errors (not suggesting you are dishonest). Best if you correct your error in closing the Q, allowing an A.  b) Your A's may have Referential Integrity (physical), they do not have Relational Integrity (logical), and easily supplied in `relational` & `mysql`. – PerformanceDBA Jan 03 '22 at 22:10
  • 1
    @PerformanceDBA, We differ in opinion on this matter. – Bill Karwin Jan 03 '22 at 23:03
  • Does this answer your question? [Possible to do a MySQL foreign key to one of two possible tables?](https://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables) – philipxy Jan 06 '22 at 07:50
  • Such rearrangements between designs for subtyping/polymorphism are not DB normalization/denormalization. Normalization to higher NFs replaces a base by projections of it, that natural join back to it. – philipxy Jan 29 '22 at 06:02

0 Answers0