1

I am integrating a friends system for my website...

One question I have about the mysql table is how it is to be set up.

Will I have many columns for each friend they add, such as freind 1, 2, 3, 4, 5 and so on and the row will be the user who has added them.

Is there a better way to do this?? With my first thought doing it that way would mean a cap on the amount of friends someone can have...

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
carlgcoder
  • 229
  • 1
  • 3
  • 10
  • Doing that you would have a 1-row table wouldn't you ? That is not really clever DB design. I'm thinking about something like 1 table (id, friend.id, friend_id_who_added) and other table friend (id, name) – Trefex Aug 27 '11 at 15:12

2 Answers2

3

Your suggestion of multiple columns would violate first normal form. As well as artificially restricting the number of friends such structures are tedious to work with and inefficient.

You need a junction table for friends with 2 columns (userid1,userid2) that form a composite primary key. Both columns would have a FK relationship with the user table.

In many RDBMSs you would also have a check constraint userid1 > userid2 so a relationship can only be stored in one form but I believe check constraints are somewhat broken in MySQL.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • +1: Only caveat is the composite primary key won't stop reverse duplicates. IE: 1,2 and 2,1 are considered valid composite PK values – OMG Ponies Aug 27 '11 at 15:12
  • Ok do you have any links to where I could learn this technique, thanks – carlgcoder Aug 27 '11 at 15:12
  • @carlgoder - The gist of my answer is really about normalisation. Worth reading the wiki articles on [first](http://en.wikipedia.org/wiki/First_normal_form), [second](http://en.wikipedia.org/wiki/Second_normal_form) and [third](http://en.wikipedia.org/wiki/Third_normal_form) normal forms. – Martin Smith Aug 27 '11 at 15:16
2

You will need an additional table with records for each friend a member has. For example:

members (id, name)
friends (member1, member2)
Vladimir
  • 818
  • 5
  • 13