Linear vs Normalized Table

[rulian]

I have a decision to make.

I need to let users link to each other (friend each other) on a site. I'm trying to develop a system where getting friends, common friends and all that jazz can be easily manipulated and updated.

However, I'm tossed between whether creating a seperate friendship table to contain all the relationships (which can be quite numerous given the amount of user and possible combinations) or having each member account table hold a list of user friends that I can load and handle in PHP

Meaning instead of having a table like so to keep friends relationships

| ID | Friend 1 | Status | Friend 2 | Status
| 1 | 2923 | C | 2983 | P -- Pending request
| 2 | 2543 | C | 2223 | C -- Confirmed Friends
| 3 | 2523 | C | 2233 | D -- Denied request
| 4 | 2523 | X | 2233 | D -- Member Deleted

have it only to establish the connection then move the ID's to the user's account table

acct table:

| ID | ..... basic info | FRIENDS |
| 3232 | name, age | 3432, 3928, 239, 2003 | --- meaning 4 friends
| 3432 | name, age | 3232, 3928, 239, 2003 | --- meaning 4 friends

So 2 viewers with each others id's in their accounts woudl be friends.

I think this is obviously a less efficient way, but wont eat up millions of rows in a relational table.

What are your thoughts on approching this problem?

[devinemke]

what you have there is a classic many-to-many data relationship. you should use a separate link table.

[Protato]

Rulian,

Keep them separated. The amount of data isn't going to change, by using the one table design you are just cramming it into a harder to read place. With the separated tables you get the benefit of using foreign keys and inner joins, which is easier to maintain and integrate into your scripts.

If you planned on keeping the friends list in the single table, how would you tell each friends status? Another comma delimited field? Seems to me the php to get those into arrays and compare to get only active friends (not a pending/invited status) would be a little more time consuming then a sql statement with

"... WHERE `member_id` = '1' AND `status` = '1'"
[code]