5

I want to create a friends system (something like in facebook).

I want to save relationship data in MySql, but I do not know which way is better:

  1. To save everysingle relationship as a single entry, such as:

    id | people1 | people2
1  | john    | maria
2  | john    | fred
3  | maria   | fred

    (there i declare relationships between all of these 3 peoples)

  2. To save everyone name and list his friends:

    id | people | friends
1  | fred   | mary, john
2  | mary   | john, fred
3  | john   | fred, mary

Or maybe there is better way?

Alex K.
  • 171,639
  • 30
  • 264
  • 288
Mindaugas Jakubauskas
  • 439
  • 1
  • 4
  • 16
  • I would say, option one, your db gets bigger, but I dont forsee any problems i see when i would do it like option 2, inserting and editting in option 2 would make it alot harder. Persnoal oppinion this. – Dorvalla Oct 22 '12 at 12:41
  • #2 is rightly considered bad practice, see http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad – Alex K. Oct 22 '12 at 12:47
  • absolutely sure - the first way. although - use numeric ID numbers for the people, not names. – Randy Oct 22 '12 at 12:41

2 Answers2

11

No Dear,

you just need one single table for make friend relationship. structure is following i have used

id (primary key) | my_id( integer logged user id ) | friend_id ( integer user id of another user he will receive friend request from logged user)

like we have two users in our users table then we have two entries for both user to make relation with each other 

id | name | age
1  | vipan | 12
2  | karan | 12

then entry should be

id | my_id | friend_id
 1     1        2
 2     2        1

Please don't vote down in any case but i have use this table structure in my site and this is same structure used in joomsocial this is best table structure i think so i use it and please don't use comma separated values in table they will make problem in joins and relationship in some cases

Please see 4 number comment in this following link of post

Separate comma separated values from mysql table

Community
  • 1
  • 1
  • 1
    Please see 4 number comment in this post http://stackoverflow.com/questions/9906430/separate-comma-separated-values-from-mysql-table –  Oct 22 '12 at 12:49
  • but i will need to print all the friends of some person, so i will need names and it doesn't look very fast to retrieve name of each person from another table (if count of friends will be above 500 and entries in users table above 100k) – Mindaugas Jakubauskas Oct 22 '12 at 14:20
4

The first one is the best no doubt cause the second one would not respect the first normal form.

You have to avoid multiple values in the same column cause it will get really painful to edit

Here's the link about database normalization. Most of the time, we respect the third normal form cause it's a good compromise between normalization and performance.

Also, like Randy said, you have to use the IDs so then you can link them with a foreign key.

Community
  • 1
  • 1
Marc
  • 16,170
  • 20
  • 76
  • 119
  • but i will need to print all the friends of some person, so i will need names and it doesn't look very fast to retrieve name of each person from another table (if count of friends will be above 500 and entries in users table above 100k) – Mindaugas Jakubauskas Oct 22 '12 at 20:31
  • Well, a join doesn't cost that much and trust me mysql can handle 100k rows pretty easy so for this case I wouldn't sacrifice normalization for performance. It wouldn't respect the first normal form – Marc Oct 23 '12 at 12:21
  • @MindaugasJakubauskas Also, example: You need to know if john is a friend of Robert... You gonna start parsing all the string and spliting? That's not really good for performance and it's painful. Instead, witht he option 1, you just do a normal select and if you set your indexes the good way, it wont even take a second. – Marc Oct 23 '12 at 12:29