Reputation: 117587
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
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
Reputation: 25725
See: http://www.sqlfiddle.com/#!8/3519e/4
I have made an example with 3 tables:
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')
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