How to manage an AJAX based 'like/dislike' feature?

Question

I'd like to add a like/dislike-upvote/downovote-type feature to each one of the posts in the forum script I'm writing (much like the one here in SO). I'm having two difficulties trying to figure out how it can be done:

1) I can't figure out a db schema that'd do it efficiently. I could use a separate \likeordislike\ table to make a relation between user and post (xyz likes post #123), or I can use a column of type \'text\' in the \posts\ table listing out all the users who have liked (or disliked) the post. The latter of course means I'd have to parse the field for userIDs to make any use of it.

2) Make sure the user doesn't get to like/dislike a post twice.

It's probably trivial but I can only think of ways that make a lot of mysql calls on server side processes. Thanks.

Answer 1

Make a separate table in which you keep track of who likes something and who doesn't. That table will be used to check if a user already did something, so you can prevent him doing it twice. Then add another field (if you will have votes) or two (if you will have likes/dislikes) in which you will store the total amount of likes/dislikes or score, so you don't have to calculate this on-the-fly every time you display the post. And you will, off course, update this column (or columns) when somebody votes on the post.

And don't bother disabling the vote link. Just check if the user has already voted when he clicks on the link and deny him the vote if he already cast one.

Answer 2

(Similar answer to Jan Hančič here, but I decided my take on the ratings was different enough...)

Your initial thought of a separate table to store likes/dislikes is absolutely the way I would go. I'd put indexes on the two main columns (player ID and post ID) which is critical to the next bit.

For example:

create table users (
    userId varchar(254) not null,
    -- ...
    primary key (userId)
)
ENGINE=...;

create table posts (
    postId int(11) not null,
    title varchar(254) not null,
    content text not null,
    -- ...
    primary key (postId)
)
ENGINE=...;

create table userPostRatings (
    userId varchar(254) not null,
    postId int(11) not null,
    rating int(2) not null,
    -- ...
)
ENGINE=...;

create index userPostRatings_userId on userPostRatings(userId);
create index userPostRatings_postId on userPostRatings(postId);

I'd then use a joined query (whether in a stored procedure, in the code, or in a view) to get the post information, e.g.:

select p.postId, p.title, p.content, avg(r.rating)
from posts p
left outer join userPostRatings r on p.postId = r.postId
where p.postId = ?
group by p.postId;

(That will return NULL for the average for any posts that don't have any ratings yet.)

Although this is a join, because of the indexes on the userPostRatings table it's a fairly efficient one.

If you find that the join is killing you (very high concurrency sites do), then you may want to de-normalize a bit in the way Jan suggested, by adding an average rating column to posts and keeping it updated. You'd just change your query to use it. But I wouldn't start out denormalized, it's more code and arguably premature optimisation. Your database is there to keep track of this information; as soon as you duplicate the information it's tracking, you're introducing maintenance and synchronisation issues. Those may be justified in the end, but I wouldn't start out assuming my DB couldn't help me with this. Making the adjustment (if you plan ahead for it) if the join is a problem in your particular situation isn't a big deal.