CODEWITHSUNDEEP
mysqldatabase-design
daniel
daniel

Reputation: 1222

Is it a good idea to have Multiple FKs in one field in MySQL?

I'm setting up a database and I'm interested in having a facebook-like friends system. My original plan was to have a table like so:

uid  friends
4    30,23,12,33
30   54,92,108

with all these numbers being FK's to tables with user information. I was told that this is inadvisable and practically impossible since MySQL will only deal with FK's well if they're the only placed one in a cell. So maybe something like this?

uid(PK) friend
4        30
4        23
4        12
30       54
30       92
30       108

ect. Won't this leave me with an enormous number of rows? (tens of thousands?) Is the first technique not worth it in terms of time and efficiency?

Upvotes: 0

Views: 143

Answers (4)

James Scriven
James Scriven

Reputation: 8144

For a slightly more theoretical explaination why this is bad, read http://en.wikipedia.org/wiki/First_normal_form

Upvotes: 0

jonstjohn
jonstjohn

Reputation: 60296

Not worth the time and efficiency? You will gain much more efficiency if you use the second method.

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60518

I would say that the second way is indeed the "right" way to do it - and ultimately superior to the first way you mentioned in almost every way. And yes, it will leave you with an enormous number of rows.

If indexed, it should still be very fast though - up to a point (maybe hundreds of thousands or perhaps a few million rows). Beyond that and you'll want to start looking into partitioning or other more advanced techniques.

Upvotes: 2

cdonner
cdonner

Reputation: 37688

10's of thousands of rows is peanuts, even for Mysql. There is no other way to model a many-to-many relationship. You will have indexes on these ids, which perform better by many orders of magnitude that a substring comparison.

Upvotes: 5

Related Questions