Storing 'Friend List'

DarkZlayer · #1 (**permalink**) 08-10-10, 17:20

A friend list isn't what I'm going for, but it's the same 'situation', and it saves having to first explain my idea.

I'm just fooling around learning php\mysql and I cannot figure out how to design it.

There are two ideas I have, but frankly I definitely don't think either are good:

1. Have a column for 'friends' for each user and add each new friend in the column. Then I'd have to parse the information every time from the table (which definitely sounds like a bad idea)

2. Simply store every accepted friend into a separate table. This kind of seems like it'd be hectic in the long run though. For this fooling around\learning project it'd work, but if I ever move onto 'real' projects idk if this would be best.

Surely there is some way to do this that I'm not thinking of?

Thanks!

gvee · #2 (**permalink**) 08-10-10, 17:51

Option 2 for sure.

healdem · #3 (**permalink**) 08-11-10, 03:47

My reading of Option 2 would be a deffo don't use Option 2, you dont want to be having separate tables for each friend.

friends are people/persons (unless you live in the hills where anything on two or 4 legs is accepted as fair game)

so I'd suggest you have a table which identifies people
and another table which associates specific people with other people, lets be dull an call that table friendships. it has the foreign keys that point back to the person table

eg
table: Persons
ID: an autogenerated number identifying this record
Title:
Forenames:
Surnames:
KnownAs:
....and so on

table: Freindships
PersonID: a foreign key to Persons pointing to a Persons.ID
IsFreindsWith: a foreign key to Persons pointing to a Persons.ID

make PersonID and IsFriendsWith the primary key in Friendships so that means the same person cannot be friends with the same person more than once
PersonID and IsFriendsWith must be not null and the same datatype as the Person.ID

you may want to consider if a person can be friends with themselves, or whetrehr you want to put a rule that says PersonID and IsFriendsWith cannot be the same

there is an exposure that Person A may be registered as a friend with Person F, and you may also get Person F may be registered as a friend with Person A

retrieving the details of friendships becomes relatively trivial
to find who is freinds with Person A you issue a select that looks for Person A's ID in either Friendships.PersonID or Friendships.IsFriendsWith

gvee · #4 (**permalink**) 08-11-10, 04:13

Quote:

Originally Posted by healdem

My reading of Option 2 would be a deffo don't use Option 2, you dont want to be having separate tables for each friend.

Ooh, I hadn't realised that was option 2!

In which case, yeah, definately don't use that option either!

DarkZlayer · #5 (**permalink**) 08-11-10, 08:02

Thank you Gvee and Healdem.

That's what I meant for option 2, but I must have made it unclear trying to keep it short! Definitely appreciate the overview of it though.

Time to go and get this all working now.

gvee · #6 (**permalink**) 08-11-10, 08:58

Quote:

Originally Posted by healdem

there is an exposure that Person A may be registered as a friend with Person F, and you may also get Person F may be registered as a friend with Person A

What about a check constraint for PersonID < IsFriendsWith ?

gvee · #7 (**permalink**) 08-11-10, 09:13

To extend on my above suggestion, pseudo code for inserting records to fir that logic:

Code:

SELECT Min(person) As PersonID
     , Max(person) As IsFriendsWith
FROM   (
        SELECT @person1 As person
        UNION ALL SELECT @person2
       ) As x

Internet Services

Web Development

Digital Marketing

Consumer Tech