I am making a website where there are different types of items such as blogs, posts, articles and so on. A user can set any one of them as his/her favorite. Now when I approach this thing, I have two options
- Make a table for user favorites for each type of object.
- Make a common table for all type of objects for all the users.
The problem with the 1st structure is that I will have to query a lot of tables for displaying the favorites of a particular user. But it will allow me to easily group the favorites into different categories.
However if I have to show all the favorites on one single page and merge them all, sorted according to time, then that becomes difficult. But if I use the second model, I can easily get the latest favorites, and also grouping them according to object type is not difficult, but I will have one large table site wide.
Which of the two strategies will be more scalable.
The 1st one entails multiple database queries, and the second one entails a large single table.
If it helps, I am using MySql