Re: Links between rows in a table

On Sun, Mar 06, 2005 at 20:26:50 +0100,
PFC <lists(at)boutiquenumerique(dot)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.