Designing a database for review system

database-design

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,
},
[...]

Best Answer

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}
    ]
...
}
Related Topic