To: "Stefan Weiss" <spaceman@foo.at>,
	"Bruno Wolff III" <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Links between rows in a table
References: <422A8A26.1070704@foo.at> <20050306174201.GA19019@wolff.to>
	<422B4B9A.8020606@foo.at>
Message-ID: <opsm8dm0z7th1vuj@musicbox>
Date: Sun, 06 Mar 2005 20:26:50 +0100
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <422B4B9A.8020606@foo.at>
User-Agent: Opera M2/7.54u1 (Linux, build 892)

>> 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.