Re: Database design?

On 23 Oct 2001 at 9:11, Johnny Jørgensen wrote:

> This may be slightly off topic, as it probably isnt all that
rdbm
> specific. If so, holler at me, and I'll learn..
>
> I have a bunch of entities (book,e-
book,author,country,cd,publisher)
> which are quite different in relational nature, and
therefore can't
> easily be squeezed into the same table.
>
> Common for all of them, however, is the need for a
description. Thing
> is, there needs to be descriptions in (currently) 4
different
> languages, and the count may rise.
>
> 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.
>
> Is my design fundamentally unsound, should there be a
description
> table for each of the listed entities, or what am I to do?

Maybe you could use inherited tables to achieve that.

CREATE TABLE items (
  id SERIAL,
  description TEXT,

  PRIMARY KEY (id)
)

CREATE TABLE books (
  isbn VARCHAR(15),
  other_field INTEGER,
  ...

) INHERITS (items);

CREATE TABLE cds (
  title VARCHAR(15),
  ...

) INHERITS (items);

With this you can define the "general" attributes in the
"items" table and more specific things in the specific tables.

The primary key for all those tables will be kept unique
across tables, since they will all refer to the same
SEQUENCE.

Maybe there are more elegant and correct solutions, I am
just beginning my jorney to the PgSQL world (this, for
example, couldn't be done with MySQL).

--
Ernesto Baschny <ernst@baschny.de>
 http://www.baschny.de - PGP Key:
http://www.baschny.de/pgp.txt
 Sao Paulo/Brasil - Stuttgart/Germany
 Ernst@IRCnet - ICQ# 2955403