Message-ID: <422B6315.1050904@foo.at>
Date: Sun, 06 Mar 2005 21:07:49 +0100
From: Stefan Weiss <spaceman@foo.at>
Organization: Foo Orbital Operations
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: PFC <lists@boutiquenumerique.com>
Cc: Bruno Wolff III <bruno@wolff.to>, 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> <opsm8dm0z7th1vuj@musicbox>
In-Reply-To: <opsm8dm0z7th1vuj@musicbox>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

On 2005-03-06 20:26, PFC wrote:
> 	Because your relation is symmetric, you should not name them "user" and  
> "friend".

A good point, thank you.

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

This is what we were planning to do on the application side, but a CHECK
constraint is even better. It will be used and enforced by those DB
engines that understand it, and ignored by the one engine that doesn't.

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

Thank you for your insight. We will rename the columns, add the CHECK
and go ahead with this setup.


regards,
stefan weiss