Database structure

Rails
1

Database structure question.

I’ve already got this sorted but I just want to check that there isn’t a
better alternative.

If I have 5 fixed ratings per review as columns in the table of reviews
and then 5 fixed ratings (an average of all the reviews) as columns in
the table of restaurants, is this alright?

Or should I make a separate ratings table with 5 fields. In my mind
there is little point making a separate table for ratings if there would
only ever at most be one row belonging to a review. However it would
mean less columns in the reviews and restaurants tables. Thoughts?

2

If there will always be five columns then it is more correct to just add
those on to the restaurants table. You could make another table for the
ratings, but it would add overhead and serve no purpose other than
reducing the number of columns in your main table.

You’re talking about reviews though? If there will ever be more than one
review, then you should make that a separate table.

Jason

3

Jason N. wrote:

If there will always be five columns then it is more correct to just add
those on to the restaurants table. You could make another table for the
ratings, but it would add overhead and serve no purpose other than
reducing the number of columns in your main table.

You’re talking about reviews though? If there will ever be more than one
review, then you should make that a separate table.

Jason

Thanks this is what I thought, so it is good to hear.

Yeah I already have a table for the actual reviews themselves.

4

Richard wrote:

Database structure question.

I’ve already got this sorted but I just want to check that there isn’t a
better alternative.

If I have 5 fixed ratings per review as columns in the table of reviews
and then 5 fixed ratings (an average of all the reviews) as columns in
the table of restaurants, is this alright?

Or should I make a separate ratings table with 5 fields. In my mind
there is little point making a separate table for ratings if there would
only ever at most be one row belonging to a review. However it would
mean less columns in the reviews and restaurants tables. Thoughts?

Just make sure that your average rating never gets out of step with the
ones stored on the review table. An alternative would be to get the
average from the reviews table with some SQL that uses the avg function.
That way your average will always be accurate.

5

Jonathan T. wrote:

Richard wrote:

Database structure question.

I’ve already got this sorted but I just want to check that there isn’t a
better alternative.

If I have 5 fixed ratings per review as columns in the table of reviews
and then 5 fixed ratings (an average of all the reviews) as columns in
the table of restaurants, is this alright?

Or should I make a separate ratings table with 5 fields. In my mind
there is little point making a separate table for ratings if there would
only ever at most be one row belonging to a review. However it would
mean less columns in the reviews and restaurants tables. Thoughts?

Just make sure that your average rating never gets out of step with the
ones stored on the review table. An alternative would be to get the
average from the reviews table with some SQL that uses the avg function.
That way your average will always be accurate.

Well everytime someone submits a review it calculates and updates the
average, so that should be ok right?