From: Andreas Pflug <pgadmin@pse-consulting.de>
To: Rodrigo Madera <rodrigo.madera@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Faster db architecture for a twisted table.
Date: Sun, 04 Dec 2005 00:57:07 +0100
Message-ID: <439230D3.9070006@pse-consulting.de> (raw)
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
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
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: pgadmin@pse-consulting.de, rodrigo.madera@gmail.com
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <439230D3.9070006@pse-consulting.de>
* 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