From: PFC <lists@boutiquenumerique.com>
To: Stefan Weiss <spaceman@foo.at>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Links between rows in a table
Date: Sun, 06 Mar 2005 20:26:50 +0100
Message-ID: <opsm8dm0z7th1vuj@musicbox> (raw)
In-Reply-To: <422B4B9A.8020606@foo.at>
References: <422A8A26.1070704@foo.at>
<20050306174201.GA19019@wolff.to>
<422B4B9A.8020606@foo.at>
>> It would probably be better to always have either both or neither of
>> the symmetric relationships in the table. You could make a set of
>> triggers
>> to enforce this.
Because your relation is symmetric, you should not name them "user" and
"friend".
The duplication is useless if you add a constraint : see this
create table friendship (
user_id_1 integer references ... on delete cascade,
user_id_2 integer references ... on delete cascade,
CHECK( user_id_1 < user_id_2 )
);
user_id_1 < user_id_2 means :
- a user can't be his own friend
- only one row per friend
- when you want to know if A is friend of B, no need to make two selects,
just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 =
max(user_id_A, user_id_B)
To get the list of friends for a user, you still need the union, but that
is no real problem. Making two queries will be marginally slower than one
query on a bigger table, but youu save precious cache space, so in the end
it could be faster.
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-sql@postgresql.org
Cc: lists@boutiquenumerique.com, spaceman@foo.at, bruno@wolff.to
Subject: Re: Links between rows in a table
In-Reply-To: <opsm8dm0z7th1vuj@musicbox>
* 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