I'm am building a site where I have users and want to relate them to each other. Like lets say they could become "Friends".

My question is should I have a table called "Friendships" and each time there is a new friendship I would add a row to this database. (Field 1: user, Field 2: Friend 2) or should I have a field in the user table that separates all friends ie(Field Friends: Friend 1, Friend 2, Friend 3).

I know keeping relationships in a comma field goes against a relational database model, but I thought after a certain point (> 500,000 friendships) it would probably process faster than searching the other database?

Any comments on using commas to separate relationships would help. Thanks so much in advance.

Dan

Use separate fields and just add a new record for each new friend ie

Code:

Create table Friends(
     user_id
     friend_id
)

Using your other method it would be a right pain to say find out who has Fred as a friend or if you wanted to delete Fred from the system and all references to him. Normalising your data makes it easier for you to look after your data. 500k friends??? that's a hell of a Christmas card list.

Mike

Thanks so much for your response. That makes a lot of sense and solves my issue. You're right deleting a person out of the system would be a nightmare.

I meant 500,000 relationships, but even that seems like it would be quite a bit. haha

Thanks again,
Dan