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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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