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