Re: Links between rows in a table

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