Faster db architecture for a twisted table.

From: Rodrigo Madera <rodrigo(dot)madera(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Faster db architecture for a twisted table.
Date: 2005-12-03 23:00:21
Message-ID: 3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mitch Skinner 2005-12-03 23:29:15 Re: Database restore speed
Previous Message Luke Lonergan 2005-12-03 21:29:01 Re: Database restore speed