3

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.

5

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.

  • 2
    Adding a column Comment to the Votes table (like you said in another answer) is also a good idea. Thank you! I might also add that these votes are cast on a specific ProductVersion rather than the Product itself. And the overall Products likes/dislikes/votes/whatever is the sum of all the versions votes... If that makes sense? ;) – SeToY Oct 15 '13 at 13:23
  • upvoted! how would vote work if you had something like dislike love and anger – PirateApp Nov 21 '19 at 12:50
  • You could have Dislike, Love, and Anger as allowable values for vote. Whether you make this a foreign key or use an Enum type or even an integer depends on how you want to physically model this in your DBMS. – Colin 't Hart Nov 21 '19 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.