Re: Foreign key pointing to multiple tables

Chris wrote:
>
> Basicly I have a table called Jobs. Each Job has some details in its
> table, it also has a type. These are A, B, C, D, E and F. If it is
> of type A then it needs an additional 10 columns, B 13, C 30 and so
> on. These additional piecies of information are totally unrelated to
> those in a different letter. How do I represent this?
>
> My first thought was to have a Jobs table which has a foreign key
> pointing to a Type table, which lists the types, and then a column
> which has a foreign key to either Table A, Table B, Table C etc. This
> would them mean that there was a foreign key that pointed to different
> tables depending on what another column's data said.

That's not really a foreign key because these always point to the same table. The usual solution is to simply reverse them and give the tables Table_A, Table_B, et cetera, a foreign key (which is identical to their primary key) and that points to the primary key of the Jobs table. This may look like the wrong way around but you get real foreign keys that way which are easier for the DBMS to maintain.

• Jan Hidders