Skip site navigation (1) Skip section navigation (2)

set-of-pointer valued attributes - help!

From: Nico D <nico(at)mclink(dot)it>
To: pgsql-hackers(at)hub(dot)org
Subject: set-of-pointer valued attributes - help!
Date: 2000-08-02 06:57:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
I am wondering what's the best way to to build a database where rows of tables
are like objects.
An object (a row of table t1) can have a collection of
pointers to other objects (rows of table t2).
To make an analogy, a man has a collection of thousands of
estates which can also be shared with other people.

How to represent that?
Suppose I have a table People and a table Estates.
If Alice has 400 properties, I would have to replicate the
row Alice 400 times, each row pointing to a different
estate (bad thing). Otherwise, I would  have to make another
table only for the association between the two tables, but
then :
1) the efficiency is lower (too many accesses to the
intermediary table and too many joins)
2) the SQL clarity is lower
3) too many intermediate tables: an object can have more
than one collection of objects

If there's no better way to do the things, then

- Proposal -

Add a new internal featured type : set

So that, say, if Harry has a new estate, one would have just to:
1) add the estate in table Estates, if it doesn't exist
2) get the oid of the estate
3) add the oid in the field set_of_estate_oids in the Harry's row
in table People

If one wants to list all Harry's estate, one has simply to
express a SQL query like :

SELECT People.*, Estates.*
FROM People, Estates
WHERE People.set_of_estate_oids = Estates.oid

note that the field People.set_of_estate_oids is not single
valued but is set-valued, the parser should understand it
and should behave as if there were many rows with that field

Without that feature anyway, I might proceduraly redefine the operator
'=' but I don't know how to make iterative the query by
itself. I mean, how to span the above query in more or
less something like this:

( SELECT People.*, Estates.*
FROM People, Estates
WHERE People.set_of_estate_oids[1] = Estates.oid ) UNION \

( SELECT People.*, Estates.*
FROM People, Estates
WHERE People.set_of_estate_oids[2] = Estates.oid ) UNION \

( SELECT People.*, Estates.*
FROM People, Estates
WHERE People.set_of_estate_oids[3] = Estates.oid )

and so on for every element contained in the set field.

Many thanks in advance,

IT u.g. engineering student

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2000-08-02 07:07:06
Subject: Re: mac.c
Previous:From: Guo BinDate: 2000-08-02 06:54:44
Subject: Re: random() function produces wrong range

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group