In a project in which I need to implement like/unlike functionality, I want to know which approach would better than other from performance point of view. If I have to go with insert delete, then what would happen if someone like/unlike it so many times (Then probably most of primary key numbers will be occupied based on like/unlike)?
Does anyone know how social networking sites do it?
EDIT:
My table is as below:
CREATE TABLE `junc_user_share_like` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`jusid` int(10) unsigned NOT NULL,
`liker` mediumint(8) unsigned NOT NULL,
`when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `jusid` (`jusid`,`liker`),
KEY `liker` (`liker`),
CONSTRAINT `junc_user_share_like_ibfk_1` FOREIGN KEY (`liker`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `junc_user_share_like_ibfk_2` FOREIGN KEY (`jusid`) REFERENCES `junc_user_share` (`jusid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
EDIT 2:
As you said with last updates this is what I've inferred:
INSERT INTO likes SET jusid=$jusid, liker=$liker
UPDATE junc_user_share SET likes=likes+1 WHERE id=$id
This works great for likes, but what if someone wants to undo his like? Should I delete record from likes? My problem is exactly here? What to do here?
Denormalize.
likes
and a unlikes
field on the items table, both starting with 0On a like/unlike do (assuming 1=like, 0=unlike)
INSERT INTO likes_unlikes SET item=[id of item], user=[id of user], action=[0|1];
UPDATE items SET likes=IF([action]=1,likes+1,likes), unlikes=IF([action]=0,unlikes+1,unlikes) WHERE id=[id of item]
A UNIQUE KEY
on likes_unlikes(item,user)
gives you uniqueness as a present.
Edit
After discussion, if you just have likes, but no dislikes, the situation is even easier:
likes
table is needed with item and user idlikes
field is needed on the items tablethe queries are now something like
INSERT INTO likes SET item=[id of item], user=[id of user];
UPDATE items SET likes=likes+1 WHERE id=[id of item]
and for reversal of a like:
DELETE FROM likes WHERE item=[id of item] AND user=[id of user];
UPDATE items SET likes=likes-1 WHERE id=[id of item]