CODEWITHSUNDEEP
mysqlsqldatabase-designrelational-databaseforeign-key-relationship
Ariyan
Ariyan

Reputation: 15158

Foreign Key From two tables in one column in SQL

I have a order table and each order in that table belongs to either a device or part (these two have no specific intersection in fields).
So I need to store the did or pid as FK in order table. 

"device"
+----+....
| id |<---------+
+----+....      :
: : : : .       :
                :
                :
"part"          :
+----+....      :
| id |<-------+ :
+----+....    : :
: : : : .     : :
              : :
              : :
     "order"  @ @
     +-----+-------+....
     | id  |  for  |....
     +-----+-------+....
     : : : : : : : : .

Now How should I do this?

Which one is the best method? or some other method?

Upvotes: 2

Views: 720

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Either use exclusive foreign keys or inheritance, as explained here. Just be careful about CHECK, which is not enforced by MySQL, so you'll have to "emulate" it via trigger.

Please don't do the "type" approach, it can lead to problems.

Upvotes: 2

Jay
Jay

Reputation: 2230

I would go with two tables, one for the order and the other for item. Item table will have ID, type (part or device; P or D) and the rest of the details. In the order table, I will just have a FK to the Item Table ID.

Upvotes: 1

Linger
Linger

Reputation: 15058

If it was me, I would create both the following FK fields: did and pid. You would always have a value for one and not the other.

Your query would look something like the following:

SELECT o.*, COALESCE(p.descr, d.descr) AS Description,
            COALESCE(p.number, d.number) AS PNumber
FROM order o
LEFT JOIN device d ON o.did = d.id
LEFT JOIN part p ON o.pid = p.id

Upvotes: 0

Related Questions