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.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
