How to store data like as facebook's "likes"

Question

I have object which store in database, it's a some text with properties. That text has rating. I need to store this rating, and prevent to one user raise this raiting more than one time. If I store "text id" and "user id" in other table and count all records which have needing "text id" i have too much records in table.

Answer 1

There are two ways:

1. You can use many-to-many relationship ie use separate table with name like 'user_likes', it will have user_id and like_id columns, both of them are primary key (it makes possible user to like the like_object only once)

2. Another way - which hightraffic websites use: every user record in user table has columns: likes which is just serialized array or json, whatever. Before update this columns your application retrieve this data and look for particular like_object_id if it doesn't exist - you update your database. Please note that in this case all care about data consistency in your application (for instance like_object_id exists in some user record, but doesn't exist in like_object table) should be implemented in your application code, not database.

P.S. Sorry for my english, but I tried to explain as best as I could.

Answer 2

If I store "text id" and "user id" in other table and count all records which have needing "text id" i have too much records in table.

How do you know what is too many records?

Some of the MySQL tables I support have billions of rows. If they need more than that, they split the data to multiple MySQL servers. 1 million rows is not a problem for a MySQL database.

If you want to limit the data so each user can "like" a given text only once, you must store the data separately for each user. This is also true if a user can "unlike" a text they had previously liked.

CREATE TABLE likes (
  user_id BIGINT UNSIGNED NOT NULL,
  post_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, post_id),
  KEY (post_id, user_id)
);

This example table uses its primary key constraint to ensure each user can like a given post only once. By adding a second index, this helps to optimize queries for likes on a specific post.

This is only 16 bytes per row, plus the size of the index. I filled an InnoDB table with over 1 million rows, and it uses about 60MB.

mysql> show table status\G

           Name: likes
         Engine: InnoDB
           Rows: 1046760
    Data_length: 39419904
   Index_length: 23658496

It's common to store databases on terabyte-sized storage these days, so a 60MB table doesn't seem too large.

Answer 3

I store the likes with the post itself, but not sure with its performance since non of my websites reached a very heavy load.

but I do the following :

Post {
id int;
likes_count int; // likes count to quickly retrive it
likes string; // id of the users liked this post, comma separated
} 

when a user likes a post, (using ajax):

1. the UI will update directly and show that the user liked the post

2. ajax will send request to the server with the post id and the user id, then post data will be updated as follow:

post.likes_count += 1;
post.likes += userId + ',' ;

when the user reload the page, it will check if his id is in likes, then it the post will appear as liked.