Re: Database design?

On Tue, 23 Oct 2001, [ISO-8859-1] "Johnny Jrgensen" wrote:

> This relation is of a 1-N nature for each entity, and so, i've figured
> out, I use the unique id from the (book, e-book, author etc) tables as a
> foreign key in the description table, thus forging a relation.
>
> The question (at last) is, how to have a foreign key reference more than
> one table? Obviously a description won't belong to an e-book, a country
> and a publisher at the same time, but only one of them.

If I understand your situation correctly, you may want to have an
intermediate table that provides a M-M (many-to-many) link.

It's still early enough here that I won't try to describe the solution for
your tables, but I'll give you an example. In the accounting software I've
developed, there's a need to track project billing rates by employee (or
employee class). These rates may vary from one project to another project.
What I've done is have a table with a compound primary key: employee_id*,
project_id*, rate.

You might have unique_id*, item_type*, language*, description. Here, your
table has a compound key of three fields, each of which is the primary key
to another relation. The 'description' field is then uniquely applied to one
-- and only one -- combination of the three key fields.

HTH,

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard(at)appl-ecosys(dot)com
http://www.appl-ecosys.com