Multiple values in the same column in SQL

Question

I'm fairly new to PHP and SQL. I have spent today programming a friend system (almost identical to Facebook's) but I am unsure of how I would have multiple friends in the same column. So lets say the user 'admin' has a friend called 'john' admin sends test a request and john accepts, they both go into each others friends column. But lets say another of admin friends sends him a request and he accepts it will simply overwrite 'john' with the new name, therefore only having one value in the column instead of john, another friend, etc. How would it be possible to not overwrite, but instead keep adding to the column.

I hope this makes sense and thanks in advance!

multiple friends in the same column. That's not how Facebook does it. Please read up on normalization. — Kermit
– Kermit, Commented Jan 18, 2013 at 19:32
FB has a one-to-many relationship. They have a users table and then, say, a user_friends table with two columns: user1id | user2id. Very crude example. But it is most certainly not storing all the friends in a single column in the users table. — Eli Gassert
– Eli Gassert, Commented Jan 18, 2013 at 19:32
Ok, thanks for the read. Eli, how would I go about doing this?! Thanks — iphoneapplelover123
– iphoneapplelover123, Commented Jan 18, 2013 at 19:36

Blaise Swanwick · Accepted Answer · 2013-01-18 19:51:54Z

1

What you need is a "One to Many" relationship. One 'person' can be friends with many other 'persons'.

You would have one table called person:

person_id | person_name
-----------------------
1 | John
2 | Frank
3 | Mary
4 | Oscar

And you would have a one-to-many table called friends:

person_id1 | person_id2
-----------------------------------
 1 | 2
 1 | 3
 2 | 3

So, now by looking in our friends table. Our first relationship is between John and Frank, they are friends. So is John and Mary, and Frank and Mary. Oscar has no friends.

edited Jan 18, 2013 at 19:51

answered Jan 18, 2013 at 19:35

Blaise Swanwick

1,7551 gold badge16 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kermit Over a year ago

friend_id is unnecessary. You create a composite primary key of person_id1 and person_id2 to prevent duplicate "friendships"

Collectives™ on Stack Overflow

Multiple values in the same column in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related