Re: foreign key pointing to multiple (varying) tables

>> Does this problem have a name?
>
YES ... polymorhism :-)

> 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?

depends a bit how you want to access the data ... the basic problem behind this is how you map inheritance to a relational database
If you want to get familiar with the problem family have a look at http://www.objectarchitects.de/ObjectArchitects/orpatterns/ and look at the section on mapping objects to tables ...

There's one morte question that is relevant here * do you want the full objects in the container or a list box? * or just some info that allows you to pick the latter object?

The first case is a performance killer -the second case is o.k. to handle
In this case you put the info needed for the listbox in one common table

for all samples (the table corresponding to a sample base class) and show these attributes in a list box

If somebody doubleclicks an entry you retrieve just the doubleclicked object from the
right table ... and YES ... your o/r mapper has to know which OID belongs to which
type and therefor which tables to consult in order to construct the object from the database.

> Second, what is the 'relationally correct' way to solve it?
> Or are there databases that can handle a foreign key to a
> varying table?

see the patterns - there's more than one way for mapping inheritance and

they all have specific advantages and drawbacks - as inheritance is not a concept
of the relational calculus - there is no such thing as a "relationally correct' way"

> (nb. Should/could we assign *global* identifiers and store
> those identifiers together inside each of the different
> classes?

these are known as OIDs - well understood concept.

> Then we could join all the different sample
> tables, and search by global ID. That strikes me as even
> uglier, however...)

have a look at the patterns and you will get a feeling that your problems
have been treated in extet by many people before

Cheers

Wolfgang

>
>
> Thanks!
>
> Adrian
> adrian.heilbut_at_utoronto.ca
Received on Fri Sep 29 2000 - 19:34:36 CEST