Re: Foreign key pointing to multiple tables

In message <220db75.0309170535.21a6a49b_at_posting.google.com>, Chris <chris_at_cjetech.co.uk> writes
>Hi,
>
>I have found a problem that I can't seem to solve in what I consider a
>nice way.
>
>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 perfectly valid. It only seems strange if you expect to use an identity column in the Jobs table. The foreign keys in each of the linked tables would not form a contiguous sequence.

This table layout represents a supertype with data for each subtype stored in a different table. It's a valid design that is compatible with the logical data model as you have described it.

>
>Is that even slightly valid relational database thinking? If not what
>is a better solution? All I could really think of was having an A_key
>column, a B_key column etc, but then there would be loads of null
>data.

Nulls are OK. You could create a single table with lots of fields which are ignored when the type is not A. This merges the supertype and subtype into one table. It is also a completely valid structure and compatible with the logical data model as you have described it.

Which you choose is likely to depend on implementation details. The single table design is easier to build but may eat unacceptable amounts of storage space at run-time. If there are only a few records in the table the storage overheads will be negligible. One or the other version may run faster on your particular RDBMS. Is run-time speed a big issue?

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.