foreign key pointing to multiple (varying) tables

Suppose we have a set of objects (laboratory samples) of *different classes*.
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.

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?
Second, what is the 'relationally correct' way to solve it? Or are there databases that can handle a foreign key to a varying table?

(nb. Should/could we assign *global* identifiers and store those identifiers together inside each of the different classes? Then we could join all the different sample tables, and search by global ID. That strikes me as even uglier, however...)

Thanks!

Adrian
adrian.heilbut_at_utoronto.ca Received on Wed Sep 27 2000 - 06:14:35 CEST