From: Rodrigo Madera <rodrigo.madera@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Faster db architecture for a twisted table.
Date: Sat, 3 Dec 2005 23:00:21 +0000
Message-ID: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rodrigo.madera@gmail.com
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox