Separate table or same table?

Question

So I basically have a reviews table, and for ratings I have more than 8 factors. Because my reviews table already has 5 fields, is it better for me to just create an entire new table for ratings or add it on to the reviews table too?

Update: I should use the word criteria instead of factor. Ratings 1-5 - Humor, Readability, Customer Service, etc... Oh and some would be user inputs. For example, Who helped you? Max Powers

Answer 1

I think you should create a separate table for it. This would make things clear for you and it won't create any overhead too.

Answer 2

There is a theory called Database normalization. You should use the third normal form one to create your database which should lead to a separate table for your ratings.

While the wikipedia article is indeed a bit confusing, this tutorial isn't.

Answer 3

no need to partition the review entity into multiple tables, unless you have to support an arbitrary set of factors (in the second case it would be a 1:N relationship between reviews and factors).

if your review is always composed of 8 attributes, for example, those would be distinct properties in the same table. different factors could have different data types (service 1-10, lighting: "dark/bright", handicap-accessible: "yes/no", cuisine: "chinese") etc., so you wouldn't necessarily be able to easily move them into a sep table, because they aren't the same entity type.

Answer 4

Personally, i would have a separate table for the ratings, and i would consider going one step further - instead of having the 8 different ratings factors as columns, have them as rows instead. So your tables would look like this:

Rating Table
------------
PrimaryKey
ReviewForiegnKey
RatingTypeForiegnKey
RatingValue

RatingType Table
----------------
PrimaryKey
RatingType

That way your Rating system is expandable without changing the previously entered data - just add another RatingType in the RatingType table, and start entering rows for it in the Rating table.

Answer 5

I totally agree with what Henrik said about normalization, great link!

Simply put, normalization can be summarized, as a joke, with this: "The whole key, nothing but the key, so help me Codd" (Codd is the dude that invented relational databases).

Every table has to have the "whole key" meaning it has to contain all the information about a certain entity.

It also must have "nothing but the key" so you can't store data about two different entities in one table.

In which case, whether rating should be placed in a separate table or not depends on its nature. Is it a part of ratings? Is there any good reason why you wand to put it in another table or is it just 'cause the table has "too many fields"?

Personally, I don't see any reason why it should be put in a separate table, unless you wanna follow "good practices" and put it in a separate table to meet the 3rd normal form.