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

This seems to be exactly what i needed at first glance.

---

>- Child columns are not available to the parent (does not inherit up).

But I can read a child columns using oid (ok, i'll maybe add that serial id
column) value that i've read from the parent table ...but still don't know
which table. (I managed to get the needed values from child tables but by
inefficient ways.)

---

Bruno Wolff III wrote:
>>If you use the suggestion to also have an animal type column in each
>>table,
>>then you can check the animal type to see which auxillary table to
>>look at.

Now i need only one column in animal table to do this. This is quite good
way. I'll maybe use it by now, until the way to determine the child table
name (or something other successful way) is found.

---

Inheritances might be useful to many other purposes, too. Thank You*2
much*x!

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: Lex Berezhny <LBerezhny@DevIS.com>
>To: Ville Jungman <ville_jungman@hotmail.com>
>CC: pgsql-novice@postgresql.org,"Martin N. Hudson"
><MHudson@DevIS.com>,Cristina Mossi Rhein <CMossiRhein@DevIS.com>
>Subject: Re: [NOVICE] Reference to multiple cols
>Date: 12 Jan 2003 12:14:51 -0500
>
>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>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


_________________________________________________________________
The new MSN 8 is here: Try it free* for 2 months
http://join.msn.com/?page=dept/dialup