Eng.Fouad
Eng.Fouad

Reputation: 117587

Single FK referes to PK from multiple tables

I have Table A, Table B, and Table C. Table C has a column which should be a FK for a PK. The PK could be either Table A's id or Table B's id. What is the best practice to design such tables?

I am thinking of creating 2 columns in Table C, where the first one would determine either Table A or Table B, and the second one represents the id of either Table A or Table B (depending on the first column). Is it the right way to do it?

Upvotes: 0

Views: 150

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

I am thinking of creating 2 columns in Table C, where the first one would determine either Table A or Table B, and the second one represents the id of either Table A or Table B (depending on the first column). Is it the right way to do it?

No. You would be preventing the DBMS from enforcing the foreign key. You'd have to enforce the FK through triggers or the application code, which is more prone to errors and potentially less performant.

Either make two FK fields (one for Table A, other for Table B) and use a CHECK1 to ensure only one of them is not NULL, or use inheritance.

More info in this post.


1 Unfortunately, MySQL parses but doesn't enforce CHECK constraints, so you'll need to use a trigger instead. This limitation doesn't exist in other DBMSes.

Upvotes: 1

Menelaos
Menelaos

Reputation: 25725

Using SQL UNION to Combine Two ResultSet with Different Joins

See: http://www.sqlfiddle.com/#!8/3519e/4

I have made an example with 3 tables:

  • CAR
  • TRUCK
  • DRIVER_ASSIGNED

You can create one query to join A with C, another to join B with C, and join the two result sets using UNION. For example:

(select * from `A`, 
`C`
where `A`.ID = `C`.`ID` and
`C`.`Type` like 'A')
UNION
(select * from `B`, 
`C`
where `B`.ID = `C`.`ID` and
`C`.`Type` like 'B')

In Relation to Classes and Sub-Classes

It seems to be that TABLE A and TABLE B are sub-types of another type/class. So, e.g. TABLE A may be cars, and TABLE B, may be trucks, but they extend vehicles.

In this case I think you need a fourth table, TABLE PARENT, that will combine the common fields of both A and B. C will use the primary key of TABLE PARENT as the foreign K.

TABLE A and B will contain both as foreign keys but maybe also as primary keys the primary key of table PARENT.

So, using my analogy with vehicles let us assume:

    TABLE A = CARS
    TABLE B = TRUCKS
    TABLE C = ASSIGNED_DRIVERS
    TABLE PARENT = VEHICLES

    TABLE VEHICLES - PARENT of A and B
    -------------
    ID (PK)
    HORSE POWER
    LICENSE PLATE
    etc...

    TABLE CARS - 
    -------------
    ID (PK)
    VEHICLE_ID (FK linking to VEHICLES.ID)
    NUMBER_SEATS
etc...

    TABLE TRUCKS - 
    -------------
    ID (PK)
    VEHICLE_ID (FK linking to VEHICLES.ID)
    HIGHT (meters)
    MAXIMUM_STORAGE_WEIGHT
etc...


    TABLE DRIVERS_ID - 
    -------------
    VEHICLE_ID (FK linking to VEHICLES.ID)
    DRIVER_OD
  START_DATE
END_DATE
etc...

So, the following method would save you from this problem and also be more semantically correct.

You can also check online documentation such as:

http://www.dssbooks.com/web/Files/LookInside/Web-DSS-Chapter-03.pdf (page 55) to see the theory between classes and subclasses.

Upvotes: 1

Related Questions