Just wondering how a "my friends" table is setup

[swapnet]

I was just wondering what is the standard way of maintaining "My Friends" db like in facebook, hi5 etc or "My Contacts" db in flickr. Do they create a seperate table and plonk in everybody's contact or do they use one column in the user's table and use comma seperated ids of all their friends. I've heard something called the ER model but since I'm new to databases I am not sure what should get me started.

I am just curious and would like to know the standard method of doing it.

[Charleh]

Well a comma seperated list of values would be exceedingly slow when compared to a seperate table - SQL already has all the functionality to do quick searches based on relational data, putting the values into a comma seperated list would just require extra (and very manual) processing when retrieving data from that list.

You would most likely just set up a new table with a userID and a friendID column. There would be multiple entries for a user each with a different friendID - then a join in SQL would return the required values

SELECT users.userID, friends.friendID FROM users LEFT JOIN friends ON users.userID = friends.userID

results:



userID friendID
1 2
1 3
1 4
1 5
2 1
2 3
2 5
3 1
3 5


etc