CODEWITHSUNDEEP
mysqldatabase-designrelationship
pemi
pemi

Reputation: 177

How to make relation between tables in mysql with this complicated situation?

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

Upvotes: 4

Views: 142

Answers (1)

A. Kalantari
A. Kalantari

Reputation: 133

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.

Upvotes: 1

Related Questions