Re: foreign key pointing to multiple (varying) tables

In article <39D174F4.63286EE8_at_utoronto.ca>,   adrian.heilbut_at_utoronto.ca wrote:
>
> Suppose we have a set of objects (laboratory samples) of
> *different classes*.
So, "samples" is a superclass.

> Each class of sample has properties and relationships
> specific to that class, so each class has its own table
> containing the objects in that class.
So, all sample-types are subclasses of "samples".

> Every sample may be placed into one or more containers,
> labelled by ContainerID. Multiple samples may also be in
> the same container. To represent the fact that a sample is
> in a container, we create an entry in another table
> Contents.
> A row in Contents has the container, and it has the ID of
> object that is being put the container. Since those objects
> are coming from a number of different tables, however, we
> also need to store the table in which that ObjectID points.
>
> Uh oh. Suppose we have a container, and we want to know
> what samples are inside it. We select all the rows in
> Contents that have the desired ContainerID, and end up with
> a bunch of links to various different tables. Is there a
> way of handling the retrieval of these foreign keys in SQL,
> or does our code need to figure out which tables to search
> dynamically?
>
> Does this problem have a name?"
The "contents" table represents the n:m relationship between "samples" and "containers". The super/subclass *problem* is aka "ISA".

> Second, what is the 'relationally correct' way to solve it?
Add an indicator in "contents" to identify the subclass -or- create a "samples" table which hold the common attributes and the subclass indicator.

> Or are there databases that can handle a foreign key to a
> varying table?
All network and relational DBMSses can handle this, where in network DBMSses it's even very simple.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 27 2000 - 11:22:31 CEST