Best way to store custom favorites in MySQL

Question

I have a photo gallery. I want to add the "Add to Favorites" button so that the user can add another user to their favorites. And then I want each user to be able to view the list of favorite users, as well as keep track of who (the list of users) added this user to their favorites.

I found two ways, and the first one:

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

I don’t like this method because of 1) many repeating 2) very large tables in the future (if they have at least 1001 users and everyone loves the other 1000 users = 1 001 000 records), which I suppose will slow down my database .

The second way:

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

I can use these favs and explode () them in php or search if the user likes another user at the request of MySQL select count(user_id) from users where favs LIKE '% 23 %' and user_id=10;

But I feel that the second method is not very “correct” in terms of MySQL.

Can you advise me something?

Answer 1

Think about it. Your argument against using the first approach is that your tables may become too large, but you then say that if you use the second approach, you can run a wildcard query to look for fields that contain something.

The second approach makes the table look completely impossible. In the first approach, you just click the indexes on each of your columns, and you're good to go. The first approach scales significantly, much, much better than the second. 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 universally and for good reason.

Edit:

Another problem is that the second approach is to separate out most of the work of maintaining the database in the application. In some cases, this is normal, but the cases you are talking about are the things the database has. You would only invent the wheel and badly.

Answer 2

Well, the second way is not so easy when you want to delete or make changes, but everything is fine with MySQL. Although, Joomla even includes different date information in the same field called params .

Answer 3

Definitely go the first way.