CODEWITHSUNDEEP
mysqlsqldatabaseperformance
Silverfall05
Silverfall05

Reputation: 1127

What is the right way of building user favourites table (Performance)

I guess that title isn't very descriptive, so I will explain! I have table called users_favs where is stored all info about which posts user has liked, which post he has favourited and the same for comments. info there is stored as serealized array / or JSON who cares.

Question: What is better? Stay like this or to make 4 tables for each of the fields and store not in serealized version but like user_id => post_id??? What I think about second option is that after some time this field will be GIGANTIC. Also, I will need to make 4 queries (or with JOINS) to take all of the info from these tables.

enter image description here

Upvotes: 0

Views: 231

Answers (2)

pratim_b
pratim_b

Reputation: 1200

I presume a scenario when you will have data for fav_categories and other columns are null. Similarly for columns fav_posts, liked_posts, liked_comments. So there is a high probability that in each row , only three columns will have data most of the time (id,user_id,any one of rest). If my assumptions are right and the use cases as well , then i would definitely go four four tables.

To add to above you can always choose from whether you want to make read-friendly or write-friendly.

Upvotes: 0

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

Keeping it in 1 table means that you'll only need 1 table access and 0 joins to get all the data. While storing it in 4 tables, you'll need at least 1 table access and n-1 joins, when you need n fields of information. Your result set at the end of the query will probably be the same, so the amount of data send over the network is independent of your table structure.

Upvotes: 1

Related Questions