Mysql table layout with multiple values for same column

Question

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...

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
– Trefex, Commented Aug 27, 2011 at 15:12

Community · Accepted Answer · 2017-05-23 11:43:19Z

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Aug 27, 2011 at 15:08

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

3 Comments

OMG Ponies Over a year ago

+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

carlgcoder Over a year ago

Ok do you have any links to where I could learn this technique, thanks

Martin Smith Over a year ago

@carlgoder - The gist of my answer is really about normalisation. Worth reading the wiki articles on first, second and third normal forms.

Vladimir · Accepted Answer · 2011-08-27 15:09:25Z

2

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

members (id, name)
friends (member1, member2)

answered Aug 27, 2011 at 15:09

Vladimir

8565 silver badges14 bronze badges

Collectives™ on Stack Overflow

Mysql table layout with multiple values for same column

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related