From: Bruno Wolff III <bruno@wolff.to>
To: PFC <lists@boutiquenumerique.com>
Cc: Stefan Weiss <spaceman@foo.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: Links between rows in a table
Date: Mon, 7 Mar 2005 10:15:55 -0600
Message-ID: <20050307161555.GA3643@wolff.to> (raw)
In-Reply-To: <opsm8dm0z7th1vuj@musicbox>
References: <422A8A26.1070704@foo.at>
<20050306174201.GA19019@wolff.to>
<422B4B9A.8020606@foo.at>
<opsm8dm0z7th1vuj@musicbox>
On Sun, Mar 06, 2005 at 20:26:50 +0100,
PFC <lists@boutiquenumerique.com> wrote:
> >>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 )
> );
The trouble with this approach is that for some ways of using this data
you will need to worry about the ordering of of the values. The advantage
of this method is that the space needed to store the data is half of
what is needed to store both pairs for each friendship.
> 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)
Note that you can't literally use 'min' and 'max' as above, as those functions
don't do that. You could use 'case' to do that.
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: bruno@wolff.to, lists@boutiquenumerique.com, spaceman@foo.at
Subject: Re: Links between rows in a table
In-Reply-To: <20050307161555.GA3643@wolff.to>
* 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