4

There is table A which contains data that refers to “a record of one of three other tables( B, C, D )” How can I make relation between them? The simplest way is to define a column ‘reference_id’ to refer to the id (of record) and a column table which refers to one of these three tables and connect to the target record with "if conditions", but I think there must be a better way to handle this situation.

image of tables

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
pemi
  • 177
  • 1
  • 1
  • 5
  • 1
    I think tables inheritance might be useful here, but unfortunately MySQL doesn't support it. You may simulate tables inheritance by creating common table for the B, C, D tables and linking B, C, D to that common table using foreign key. – Roman Hocke Jul 19 '16 at 07:55
  • thanks @RomanHocke – pemi Jul 19 '16 at 08:41
  • 1
    see this: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association/922341#922341 – Damir Sudarevic Jul 19 '16 at 11:53

1 Answers1

1

There are three solutions to this problem.

One that you already mentioned using a reference column.
The second solution is to do it in application level as Roman has mentioned.
The other one is to have three columns in the table A, each for (B,C,D). When it's referencing to a record in table B, fill the b_id column and set c_id and d_id to null. This way you can use foreign keys too.

A. Kalantari
  • 133
  • 9