CODEWITHSUNDEEP
mysqldatabase
emvein
emvein

Reputation: 307

'Likes' system database

I am developing web application where I have to implement 'Likes' system as facebook has. Application will have a few categories of products that customer can 'like'. So I have started to create database, but I stuck on one obstacle. As I understand there are two ways of doing this:

First. Create one database table with fields of "id, user_id, item_category, item_id". When user click 'like' button information will be saved in this table with various categories of products (item_category).

Second. Create several tables for certain categories of item. For instance, "tbl_item_category_1, tbl_item_category_2, tbl_item_category_3" with fields of "user_id, item_id".

Would be great to get more insight about best practices of this kind database structures. Which works faster? and more logical/practical? I will use only several categories of items.

Upvotes: 1

Views: 522

Answers (3)

Taryn
Taryn

Reputation: 247710

I would go with the first version with a table structure similar to this:

User Table: PK id
id
username

Category Table: PK id
id
categoryname

Like Table: PK both user_id and catgory_id
user_id
category_id

Here is a SQL Fiddle with demo of table structure with two sample queries to give the Total Likes by user and Total Likes by category

The second one - creating multiple tables is a terrible idea. If you have 50-100 categories trying to query those tables would be horrible. It would become completely unmanageable.

If you have multiple tables trying to get a the total likes would be:

Select count(*)
from category_1
JOIN category_2
    ON userid = userid
join category_3
    ON userid = userid
join .....

Use one table, no question.

Upvotes: 2

Polynomial
Polynomial

Reputation: 28316

The first method is the correct one. Never make multiple tables for item categories, it makes maintaining your code a nightmare, and makes queries ugly.

In fact, the general rule is that anything that is dynamic (i.e. it changes) should not be stored as a set of static objects (e.g. tables). If you think you might add a new type of 'something' later on, then you need a 'something' types table.

For example, imagine trying to get a count of how many items a user has liked. With the first method, you can just do SELECT COUNT(*) FROM likes WHERE user_id = 123, but in the second method you'd need to do a JOIN or UNION, which is bad for performance and bad for maintainability.

Upvotes: 1

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

The first method is the correct one. Because you dont know how many categories you will be having and it is very difficult to get the data.

Upvotes: 0

Related Questions