set-of-pointer valued attributes - help! - Mailing list pgsql-hackers

From Nico D
Subject set-of-pointer valued attributes - help!
Date
Msg-id 3373.000802@mclink.it
Whole thread Raw
List 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
single-valued.


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,
Nico

IT u.g. engineering student




pgsql-hackers by date:

Previous
From: Guo Bin
Date:
Subject: Re: random() function produces wrong range
Next
From: Thomas Lockhart
Date:
Subject: Re: mac.c