Best Primary Key for a friendship table.

Question

I have read a number of solutions for a mysql facebook friendship table and have decided on a fairly simple table with two fields user_a and user_b. I would then using a query with a UNION to get a list of all of a users friends (as they could be in user_a or user_b). My question now is... is it better to have a auto incrementing unique id or a compound id?

table 1)

user_a, user_b

table 2)

unique_id, user_a, user_b

Answer 1

My comments:

• either approach for the key is fine. I would prefer a compound key over surrogate key to save space and avoid additional indexes
• you may require a surrogate key though - some DALs do not work with compound keys

Update:

You may consider that friendship is a two-way street. Just because UserA has friended UserB does not mean that UserB has friended UserA. If you track both sides, it makes your queries easier. In that case you do:

Friend
-------
UserID
FriendUserID

So, you are only matching on the UserID column to get the list of the user's friends. If two users friend each other, you put two rows in the table. If one user unfriends another, you remove that one row.

Answer 2

While it is true that the compound key solution seems to be more elegant from a design perspective and less space-consuming at first glance, there are circumstances in which I'd personnaly go for an auto incremented numeric id instead.

If the friendship is referenced elsewhere, it will save more space on the long run to have a single numeric ID as a foreign key in the referencing table than a compound ID. Plus, an index on a single id will be (slightly) shorter and faster than a composite index if you query often on the friendship ID.