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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
