2

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?

  • Add a type field to order table and store both pid or did on one column(e.g. for)
  • Create an intermediate view with type,pid,did columns
  • Create a higher level table (e.g. goods) and make its PK to be FK on order,part,device

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

Ariyan
  • 14,760
  • 31
  • 112
  • 175
  • 2
    http://stackoverflow.com/questions/2895147/database-design-or-relation/2897732#2897732 http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 – Damir Sudarevic Jul 03 '14 at 11:26

3 Answers3

2

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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

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.

Jay
  • 1,980
  • 1
  • 13
  • 23
0

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
Linger
  • 14,942
  • 23
  • 52
  • 79