Re: Foreign key pointing to multiple tables

You are modeling a hierarchy among types. The Job is a super abstract type. What you want to put in TableA/B etc are concrete subtypes. What you put in the Type table are names of the subtypes.

RDBMS doesn't give you a nice way of modeling this object-oriented structure. The dirty, quick and tacky way is to slamm all of the subtypes into one table. The results is that many columns will have predictabe null values depending on the subtype. OO developers will need to create a Job abstract class and TypeAJob etc class for each subtypes and use the relevant column as the class's properties. This is highly labor-intensive. If you decide to add a subtype or add a column, they will go back to the drawing board. Separating the subtypes into different table may make the model more comprehensible, but doesn't really help. You 'll have proliferation of tables, columns, foreign keys and sql statements.

If you are not happy with the aproaches, and are open-minded for something more elegant, take a look at http://www.geocities.com/unifiedmodel which separates the hierarchy of types and the relationships among objects.

K

"Chris" <chris_at_cjetech.co.uk> wrote in message news:220db75.0309170535.21a6a49b_at_posting.google.com... 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.

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.

Cheers,

Chris Received on Mon Sep 22 2003 - 21:17:23 CEST