From: | Andreas Pflug <pgadmin(at)pse-consulting(dot)de> |
---|---|
To: | Rodrigo Madera <rodrigo(dot)madera(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Faster db architecture for a twisted table. |
Date: | 2005-12-03 23:57:07 |
Message-ID: | 439230D3.9070006@pse-consulting.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mitchell Skinner | 2005-12-04 00:02:58 | Re: Faster db architecture for a twisted table. |
Previous Message | Mitch Skinner | 2005-12-03 23:29:15 | Re: Database restore speed |