Database schema of a like system

Question

I've been thinking about a liking system for my website, and I can't determine the best way to approach it. I'm using MySQL, and right now, I have an activities table, for posts of text, pictures, videos, etc, and a comments table, for comments. You can like both comments and activity posts, so how should I go about with the columns of the like table? I am open to suggestions of changing the whole schema all together for a more coherent design.

Answer 1

Just make a table called likes with a structure like this:

`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`post_type` varchar(10) NOT NULL,
`post_id` int(11) NOT NULL,
`date` datetime NOT NULL,
 PRIMARY KEY (`id`)

Then whenever someone likes an activity post, you would insert the ID of that user, the post_type as activity, and then post_id would be the post ID (obviously).

If someone likes a comment, you would do the same thing but put comment for the post type instead.

Then to get the likes for an activity post:

SELECT * FROM `likes` WHERE post_id = 'id' AND post_type = 'activity'

You would just need to replace 'id' with the ID variable.

The same would be for comments (except you would need to change the post_type of course).

I hope this helps you get an idea on how to go about making a like system for your website :)