best way to store user's "favorites" in MySQL

Question

I have a photo gallery. I want to add "Add to favorites" button - so user can add other user to his/her favorites. And then I want each user to be able to watch his list of favorite users, as well as to be able to watch who (list of users) added this user to favorites.

I found two ways, and the first is:

faver_id faved_id
1       10
1       31
1       24 
10      1
10      24

I dont like this method because of 1) a lots of repeating 2) very large table in future (if a have at least 1001 users, and each likes other 1000 users = 1 001 000 records) which I suppose will slow down my base.

The second way is:

user_id   favs
1          1  23 34 56 87 23
10         45 32 67 54 34 88 101

I can take these favs and explode() them in php or search if user likes some other user by MySQL query select count(user_id) from users where favs LIKE '% 23 %' and user_id=10;

But I feel the second way is not very "correct" in MySQL terms.

Can you advice me something?

Answer 1

Think about this. Your argument against using the first approach is that your tables might get too big, but you then go on to say that if you use the second approach you could run a wildcard query to find fields which contain something.

The second approach forces a full table search, and is unindexable. With the first approach, you just slap indexes on each of your columns and you're good to go. The first approach scales much, much, much better than the second one. Since scaling seems to be your only concern with the first, I think the answer is obvious.

Go with the first approach. Many-to-Many tables are used everywhere, and for good reason.

Edit:

Another problem is that the second approach is handing off a lot of the work in maintaining the database off to the application. This is fine in some cases, but the cases you're talking about are things that the database excels at. You would only be reinventing the wheel, and badly.

Answer 2

Definitely go with the first way.