Re: Database design? - Mailing list pgsql-general

I suggest using inherited tables, but only with the id in the "items" table.
Many descriptions can then be bound to the "items" table with a foreign key.


Markus

> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Ernesto
> Baschny
> Gesendet: Dienstag, 23. Oktober 2001 16:43
> An: Johnny Jørgensen
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>