Designing a database for review system

Question

I am working on a Review System and I am having doubts about how to handle storing data in the database.

For this review system, there are three different ratings the users can input, I will call them ratingOne, ratingTwo and ratingThree. My current solution is having three different columns in the database called, respectively one_rating, two_rating and three_rating (where one, two and three are business informations that I prefer not to disclose).

Is there a better way for storing this data? For example, in the API request, this is handled as a sub-object in the JSON i.e. inside the POST json there is a second object that is as follows

[...],
"ratings": {
 "one": 1,
 "two": 2,
 "three": 3,
},
[...]

• Bart van Ingen Schenau: Are ratingOne, ratingTwo and ratingThree levels of rating (like "Awesome", "Really good" and "Average"), or aspects that a product/service can be rated on (like usability, quality of customer support, etc.)?

Answer 1

I would create two extra tables, ratings and rating_types.

The rating_types table has a name and id field. It would have just a few records:

id | name
1 | Secret Rating 1
2 | Secret Rating 2
3 | Secret Rating 3
4 | Overall awesomeness

The ratings table would simply tie things together, having foreign keys to rating_types and reviews:

id | review_id | type_id | rating
1 | 1 | 1 | 4
2 | 1 | 2 | 5
3 | 1 | 3 | 3
4 | 1 | 4 | 4
1 | 2 | 1 | 3
2 | 2 | 2 | 4
3 | 2 | 3 | 3
4 | 2 | 4 | 2

This makes it easier to add extra "things" on which you can rate. It also makes calculations easier.

For more details, you can read up on database normalization

If you need to be able to handle different "rating types" in different ways in your code, you could expand rating_types with a code field that could have a value you can check against.

Just a side note, but I would also re-structure the JSON API like so:

{
...
 "ratings": [
 {"type": "1", "label": "Secret Rating 1", rating: 4},
 {"type": "2", "label": "Secret Rating 2", rating: 4},
 {"type": "3", "label": "Secret Rating 3", rating: 4},
 {"type": "4", "label": "Secret Rating Overall Awesomeness", rating: 4}
 ]
...
}

Answer 2

For a review system you might not only need to store reviews but also do aggregate querying on them.

Query patterns could include

• Average rating
• Number of ratings
• Results sorted by a particular rating
• Other analytics queries you may want

You will have to balance between three parameters

• analytics querying
• consistent views (should your customers see review changes immediately when they rate)
• Scale at which reviews are updated or added.

In case your requirement is only to store the reviews currently then you can go with a document store approach (store reviews in json form).

On the other hand you may need to do aggregate querying in which case a 3 column approach makes sense.

Another factor to consider will be the API rate (how many reviews are added or updated per sec). In case this number is very high (think sites like imdb/amazon) then you need to go for the document approach, since 3 indexes on a table may bring down updates and inserts rate. After storing in a document store you can have custom views (aggregate views like avg rating etc) for your UI calculated in an async process. Finally the data can be pumped into an analytics store like HBase or Redshift for business querying