When to include related fields in a db table and when not to?

Question

I have a table of "posts" and I want to keep track of the ratings for each post. Normally, I would add a rating field with int values in that same table which references another "rating" table that holds actual rating data. Is there anything wrong with removing that rating field and calling the ratings directly from the rating table based on postID? Posts table will have no direct reference to the rating, but the rating table references the post ID. Which way is better/correct. tia

Answer 1

I think you've just become enlightened about good database design. The second approach is the correct one, the rating table has a Foreign Key PostId that references back to the Posts table.

I'm not sure how the first approach actually works, do you have single ratings field in the Posts table that references mulitple rows in the rating table? Do your ratings have a reference which isn't unique? Either way that isn't good practice, read up on normalisation and you'll get the idea.

Answer 2

You should include the rating field directly in your posts table. It is a question of normalization and you win nothing if you seperate that value because there is no functional dependency.

In some rare situations you might add such fields as seperate tables. For example if you have to add the rating value to an existing table and you are unable to add a new column for whatever reason.

Or may be you can gain some performance if the rows are very large and change very infrequent while the rating value changes very frequent.

But I would not say that your case falls into neither category; so just keep it in the posts table.

---

I am going to summarize some common situations.

Posts
========
PostId           Ratings
Text             ==========
RatingId  ====>  RatingId
                 RatingName

Use this if you have a set of possible rating values, such as 'Good', 'Average', and 'Poor' and all posts share this values. RatingValue would be a string identifed by an id.

If the rating value is a simple number, just include it in the posts table. That is probably your point. Why include a integer rating value and not a string with a rating name? The difference is as follows. The integer rating should be included because every value is a legal rating. This is not true for the string ratings. They should be moved to a seperate table, because only a limited set of strings represent legal ratings.

Posts
===========
PostId
Text
RatingValue

If you have multiple ratings per post the following is the way to go for a limited set of possible ratings.

Posts            PostRatings
========         ===========        Ratings
PostId    <====  PostId             ===========
Text             RatingId     ===>  RatingId 
                                    RatingName

If you have integer ratings you put them back into the post ratings table. You need to add an artifical primary key if you require one because there is no natural primary key.

                 PostRatings
Posts            ==============
========         [PostRatingId]
PostId    <====  PostId
Text             RatingValue

If you associate a rating with a user and allow only one rating for a given post per user you get a natural primary key again.

Posts            PostRatings
========         ===========        User
PostId    <====  PostId             ===========
Text             UserId       ===>  UserId 
                 RatingValue        Name