Re: Reference to multiple cols - Mailing list pgsql-novice

Relational databases are not particularly suited for building
heterogeneous hierarchies. You may try looking at some of the object
oriented databases which are perfect for this kind of relationship.

Having said that, you might also be satisfied with simply using the
PostgreSQL Inheritance feature. Personally, I have not yet used this on
production software but I have played with it enough to know that it
works *g*

The inheritance concept follows that you create a parent table (animal)
and then all of its children will inherit from this table. Thus, when
you do a select on the animal table, it will return all of its children,
even if they are heterogeneous.

Relevant DDL:

      1 REATE TABLE animal (
      2     name text NOT NULL
      3 );
      4
      5 CREATE TABLE dog (
      6     barking_volume integer,
      7     slobber integer
      8 )
      9 INHERITS (animal);
     10
     11 CREATE TABLE cat (
     12     lazyness integer,
     13     shedding_amount integer
     14 )
     15 INHERITS (animal);

There are two things to nice, first is that I no longer need a serial id
(if your tables interacts with other parts of the system you might want
the ID back in there though ;-), the second thing to notice is the
INHERITS declaration (lines 9 & 15) which link the two tables to the
animal table via inheritance and cause them to acquire the name column.

Relevant queries:

lex=# INSERT INTO dog VALUES ('ralf', 2, 5);
INSERT 17113 1
lex=# INSERT INTO cat VALUES ('penelope', 9, 2);
INSERT 17114 1
lex=# SELECT * FROM animal;
   name
----------
 ralf
 penelope
(2 rows)
lex=# SELECT * FROM dog;
 name | barking_volume | slobber
------+----------------+---------
 ralf |              2 |       5
(1 row)
lex=# SELECT * FROM cat;
   name   | lazyness | shedding_amount
----------+----------+-----------------
 penelope |        9 |               2
(1 row)
lex=# DELETE FROM animal;
DELETE 2

Subtle points:

  - Inserting into dog and cat makes the record available in animal.
  - Deleting from animal also deletes the record from child tables.
  - Child columns are not available to the parent (does not inherit up).
  - Parent columns are part of the child (inherits down).

Unfortunately, there seems to be no way to find the child table name of
a row retrieved from the parent table. But I played with this several
months ago and don't remember all the gory details, I will see if I can
dig up some of my queries.

If you like the approach I described above then I would encourage you to
first learn about it as much as possible before implementing it. The
reason is that it's not standard amongst RDBMSs and so there is little
experience with this kind of setup.

happy hacking,

 - lex

On Sun, 2003-01-12 at 08:06, Ville Jungman wrote:
> Thank You for help!
>
> Still i have one problem left. How can i determine which table's row
> references to the main table row if i don't want to read every referencing
> table and search the right row. That was a part of the original problem and
> idea why i first wanted that the main table had the column that references
> to other tables.
>
> ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
> tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
> usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31)
>
>
> >From: Bruno Wolff III <bruno@wolff.to>
> >To: Josh Berkus <josh@agliodbs.com>
> >CC: Ville Jungman <ville_jungman@hotmail.com>,pgsql-novice@postgresql.org
> >Subject: Re: [NOVICE] Reference to multiple cols
> >Date: Sat, 11 Jan 2003 18:51:21 -0600
> >
> >On Sat, Jan 11, 2003 at 14:00:14 -0800,
> >   Josh Berkus <josh@agliodbs.com> wrote:
> > >
> > > This should give you a system in which animal_id is the primary key for
> > > each table, and therefore there is a one-for-one relationship between
> > > the animal table and each of the dog and cat tables, and would prevent
> > > you from deleting a referenced record from the animal table.
> > >
> > > You would need an additional trigger to prevent duplication *between*
> > > the dog and cat tables.
> >
> >There is a way to do this without triggers. You add an animal type
> >field to all of the tables. The foreign keys then should use both animal id
> >and animal type (which means you need to make animal_id and animal_type
> >a combined unique key on the animal table). You add a constraint to the
> >animal
> >specific tables forcing the animal type to be the appropiate type.
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo@postgresql.org so that your
> >message can get through to the mailing list cleanly
>
>
> _________________________________________________________________
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Lex Berezhny <LBerezhny@DevIS.com>