CODEWITHSUNDEEP
language-agnosticdatabase-design
Alberto Zaccagni
Alberto Zaccagni

Reputation: 31580

What is a mantainable way of saving "star rating" in a database?

I'll use the jQuery plugin for presenting the user with a nice interface

The request is to display 5 stars, up to a total score of 10 (2 points per star).
By now I thought about using 7/10 as a format for that value, but what if at some point in the future I'll receive a request like

We would like to give users more choice, let's increase the total score to 20 (so that each star contributes with a maximum of 4 points)

I'll end up with a table with mixed values for the "star rating" column: some will be like 7/10 while others will be like 14/20.

Is it ok for you to have this difference in the database and deal with it in the logic layer to have it consistent? Or is preferred another way so that querying the table will not result in inconsistent results outside the application?
Maybe floating point values could help me, is it better to store that value as a number less than or equal to one? So in each of the two examples the resulting value stored in the database would be 0,7, as a number, not a varchar, which can be queried also outside the application.

What do you think?

Upvotes: 5

Views: 317

Answers (6)

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59503

I like the float idea, but none of the options are significantly outstanding. I only suggest that, whatever mechanism you choose, make sure the column is named appropriately.

The float option should be something like star_rating_ratio. The simple integer should be star_points_out_of_ten. The hundred point scale could be star_rating_percent.

(I've seen column names in the past that said "percent", but the data was a simple ratio. This keeps me up at night!)

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185663

There are three options:

  1. Store the value as a fraction (which is, honestly, what I'd likely go with since it's the lowest maintenance
  2. Store the value in two columns, one for the rating and one for the maximum
  3. Store the value as a single column with an assumed maximum, then update all values when that maximum changes.

The first option is the most portable, since the maximum value and the representation of the rating are entirely client-side (client-side as far as the database is concerned, not client-side as in it's done at the user's machine).

The second just seems arbitrarily complex, unless you wish to continue to display a 7/10 rating as a 7 with 10 stars rather than a 14 with 20 stars.

The third requires the least up-front work, but requires a massive database update when changes are made to the rating scale.

Just to reiterate, I recommend storing the value as a float or double in the database and applying whatever scale is appropriate on the client side..

Upvotes: 8

z-boss
z-boss

Reputation: 17608

KISS
Just store the number of points and have application logic to represent it in the stars in IU.
If requirements change in the future, you will adjust the logic and run a conversion script if needed. That's all.

Upvotes: 7

Mark Heath
Mark Heath

Reputation: 49502

I would store it as a simple integer measured out of 100. That way you have plenty of granularity in case you want to increase the accuracy of the ratings in the future. No need to change any existing stored values either.

Upvotes: 5

Ray
Ray

Reputation: 21905

I would suggest storing your scores as simple integers - if the rating is 7/10, store '7' in the db. If you then need to change it to a 20 point scale, just run an update query to double all the existing numbers. You could use floats if you need more precision, but for most ratings systems, integers should be fine.

Upvotes: 0

Tom Cabanski
Tom Cabanski

Reputation: 8018

I would use two columns: one to hold the rating and one to hold the maximum rating. Since that maximum could change in the future, I would maintain it in the row with the rest of the data. You could use short ints so the amount of space required, even for large row counts, would be very small.

Upvotes: 0

Related Questions