Vote Up/Like System for posts using MySQL

Question

I've searched for this on stackoverflow and while I've found something along these lines, I haven't found anything that really helps me, so I'll ask the question myself.

So I'm trying to implement a like button for images on my site. But I'm having a little trouble figuring out how it goes.

So far, I have this on the SQLI side of things :

CREATE TABLE if not exists `likes` (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `image_id` varchar(300) NOT NULL,
         `liker_username` varchar(50) NOT NULL,
          `liked_unliked` enum('0','1') NOT NULL, DEFAULT '0'
           PRIMARY KEY (`id`)
        );

Where id is the id of the like, image_id is the id of the image being liked, liker_username is the username of the user clicking the like button, liked_unliked is whether or not that user has liked the image (1 for liked, 0 for unliked [to make sure users only vote once]).

I have the image_id already stored in a photos table.

I've been told to put a unique index on liker_username and image_id in order to prevent users from liking an image multiple times, but I just want a second opinion.

So...is what I have so far even correct? Like I said, I'm trying to get it so that users can click the like button and like the image, but only once. Any help is appreciated. Thanks

You need to make sure that any (image_id, liker_username) combination appears only once. You can do this in your application logic, or, within mysql remove the id key and build a composite key with the pair (image_id, liker_username). Oh sorry, your unique index would do just that :) Well, this looks fine to me, then ! — mika, May 27 '14 at 17:40
Without seeing any php code, this is nothing more than table to us. Personally, I would probably not store the user name in this table, but store the user_id as a foreign key to your users table (assuming one exists). — mituw16, May 27 '14 at 17:40
It saves time in programming. On the other hand you will get a DB-Error (consider what errors you want to handle). — ratmalwer, May 27 '14 at 17:43
As far as I know, there are only two options to implement this constraint. Therefore, I see this question as a duplicate of [UNIQUE constraint vs checking before INSERT](http://stackoverflow.com/questions/21889843/unique-constraint-vs-checking-before-insert). +1 for a well-formed question nonetheless. — RandomSeed, May 27 '14 at 17:58

score 1 · Accepted Answer · answered May 27 '14 at 20:48

Looks like you're on the right track.

Like another user said, you might consider having a users table with user data and ids, and put the foreign key liker_user_id in your likes table. Your image id could also be a foreign key, with images stored in an images table.

You may consider allowing anonymous votes, one per i address, adding the ip address to the DB if the user is not logged in. You can get the users ip address with:

$_SERVER['REMOTE_ADDR'];

Vote Up/Like System for posts using MySQL

1 Answers1