Обсуждение: Faster db architecture for a twisted table.
Imagine a table named Person with "first_name" and "age". Now let's make it fancy and put a "mother" and "father" field that is a reference to the own table (Person). And to get even fuzzier, let's drop in some siblings: CREATE TABLE person( id bigint PRIMARY KEY, first_name TEXT, age INT, mother bigint REFERENCES person, father biging REFERENCES person, siblings array of bigints (don't remember the syntax, but you get the point) ); Well, this is ok, but imagine a search for "brothers of person id 34". We would have to search inside the record's 'siblings' array. Is this a bad design? is this going to be slow? What would be a better design to have these kind of relationships? (where you need several references to rows inside the table we are). Thanks for any help, Rodrigo
Rodrigo Madera wrote: >Imagine a table named Person with "first_name" and "age". > >Now let's make it fancy and put a "mother" and "father" field that is >a reference to the own table (Person). And to get even fuzzier, let's >drop in some siblings: > >CREATE TABLE person( > id bigint PRIMARY KEY, > first_name TEXT, > age INT, > mother bigint REFERENCES person, > father biging REFERENCES person, > siblings array of bigints (don't remember the syntax, but you get the point) >); > >Well, this is ok, but imagine a search for "brothers of person id >34". We would have to search inside the record's 'siblings' array. Is >this a bad design? is this going to be slow? > >What would be a better design to have these kind of relationships? >(where you need several references to rows inside the table we are). > > Create a table "sibling" with parent_id, sibling_id and appropriate FKs, allowing the model to reflect the relation. At the same time, you can drop "mother" and "father", because this relation is covered too. Regards, Andreas
----- Original Message ----- From: "Andreas Pflug" <pgadmin@pse-consulting.de> > Create a table "sibling" with parent_id, sibling_id and appropriate FKs, > allowing the model to reflect the relation. At the same time, you can drop > "mother" and "father", because this relation is covered too Something like a table describing relationships and a table reflecting relationships from both sides, I guess: create table relationship_type ( relationship_type_id serial, relationship_type_description varchar(20) ) populated with values such as: 1 Child_of 2 Father_of 3 Brother_of 4 Sister_of ... And then create table person_relationships ( source_person_id int4, relationship_type_id int4, target_person_id int4 ) populated with values such as: 1 1 2 (person 1 is child of person 2) 2 2 1 (person 2 is father of person 1) ... It requires a careful maintenance, as almost all (I'd stick with ALL) relationships will require a person to appear twice (as source and as target), but flexible and easy to query. Helder M. Vieira
Hélder M. Vieira wrote: > > ----- Original Message ----- From: "Andreas Pflug" > <pgadmin@pse-consulting.de> > >> Create a table "sibling" with parent_id, sibling_id and appropriate >> FKs, allowing the model to reflect the relation. At the same time, you >> can drop "mother" and "father", because this relation is covered too > > > > Something like a table describing relationships and a table reflecting > relationships from both sides, I guess: > > > create table relationship_type > ( > relationship_type_id serial, > relationship_type_description varchar(20) > ) > > populated with values such as: > 1 Child_of > 2 Father_of > 3 Brother_of > 4 Sister_of > ... > > > And then > > > create table person_relationships > ( > source_person_id int4, > relationship_type_id int4, > target_person_id int4 > ) > > populated with values such as: > 1 1 2 (person 1 is child of person 2) > 2 2 1 (person 2 is father of person 1) > This is an extended version, that could describe general person relations, not only family relations. Still, your your relationship_types are not precise. Since a two way relation is described, only the two Child_of and Brother/Sister are needed; the gender should be taken from the person themselves (to avoid data inconsistencies as "Mary is a brother of Lucy"). But this isn't pgsql-performances stuff any more. Regards, Andreas
On Sat, 3 Dec 2005 23:00:21 +0000, Rodrigo Madera <rodrigo.madera@gmail.com> wrote:
> Imagine a table named Person with "first_name" and "age".
>
> Now let's make it fancy and put a "mother" and "father" field that is
> a reference to the own table (Person). And to get even fuzzier, let's
> drop in some siblings:
>
> CREATE TABLE person(
> id bigint PRIMARY KEY,
> first_name TEXT,
> age INT,
> mother bigint REFERENCES person,
> father biging REFERENCES person,
> siblings array of bigints (don't remember the syntax, but you get the point)
> );
>
> Well, this is ok, but imagine a search for "brothers of person id
> 34". We would have to search inside the record's 'siblings' array. Is
> this a bad design? is this going to be slow?
Do you need the array at all?
alter table person add column gender;
select id
>from person
where gender = 'male'
and (mother = (select mother from person where id = 34)
OR father = (select father from person where id = 34))
You can change the OR depending if you want half brothers or not
> What would be a better design to have these kind of relationships?
> (where you need several references to rows inside the table we are).
We use that structure (without the sibiling array) for our systems.
Siblings are calculated from parents (in our case, livestock, there can
be hundreds). You have to be prepared to use recursive functions and
make sure that a person doesnt appear anywhere higher in their family
tree.
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
On Sat, 2005-12-03 at 23:00 +0000, Rodrigo Madera wrote:
> CREATE TABLE person(
> id bigint PRIMARY KEY,
> first_name TEXT,
> age INT,
> mother bigint REFERENCES person,
> father biging REFERENCES person,
> siblings array of bigints (don't remember the syntax, but you get the point)
> );
>
> Well, this is ok, but imagine a search for "brothers of person id
> 34". We would have to search inside the record's 'siblings' array. Is
> this a bad design? is this going to be slow?
Well, I don't know how close this example is to your actual problem, but
the siblings array is redundant, AFAICS. If you got rid of it, you
could query for full sibling brothers with something like (not tested):
select bro.* from
person p inner join person bro
on (p.mother = bro.mother)
AND (p.father = bro.father)
where
bro.sex='M' and p.id=34
...assuming you added a "sex" field, which you would need in any case to
query for brothers.
You could query for half-siblings by changing the AND into an OR, I
think.
Mitch