Model Likes / Dislikes in SQL I'm trying to implement a behaviour similar to Facebook (okay, most similar to YouTube).

In my table I got various Products, each Product can be liked or disliked by a Customer. I don't want to let the users rate (let's say between 1 and 5 stars), so a simple like or dislike is enough.

What's the best way of representing that?



I've thought about three essential tables (next to Customers):

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, CreatedOn)

ProductDislikes (Id, CustomerId, ProductId, CreatedOn)

But that would mean to have two tables with nearly the exact same data.



Another idea was to have the following:

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, IsLike, CreatedOn)

But that would mean I cannot provide a third state later on, if I decided to do so.



And my last idea:

Products (Id, Name, ...)

ProductRatings (Id, ProductId, CustomerId, Rating, CreatedOn)

Where a "like" would be a Rating of 5.0, a "dislike" one of 1.0.
I would go for a variant somewhat between your second and third options:

Products (Id, Name, ...)

ProductVotes (Id, CustomerId, ProductId, Vote, CreatedOn)


The column Vote should be constrained to only allow the values Like and Dislike or, if you prefer, 1 and -1 -- the latter having the advantage of a simpler possibility to do the sum() of the likes and dislikes to get an overall vote.