My design problem is as follows:

I am web scraping news from few news sites and storing in mysql database.

My design is as follows:

1) one website contains many categories.
2) one category contains one or more news.
3) I am going to give rating to each news article. One article at the most has one rating. but more than one articles can have same rating. Final Rating is calculated using other factors like, rating of a country, rating of particular category on news source.. etc..

considering above points, I have created following tables:

site:

SiteID (PK)
SiteRating
SiteName
SiteURL

Category

CategoryID(PK)
SiteID(FK)
CategoryName
CategoryOffsetURL


NewsArticle

ArticleID(PK)
CategoryID(FK)
RatingID(FK)
.
.
.

Rating

RatingID(PK)
NewsSourceRating
CountryRating
SummedRating (This is addition of above 2 ratings)

================================================== ==

I want to know ... is this database normalized? and will it provide optimal design for performance?

If you have suggestions, please guide me...

Thanks.

i don't understand what the Rating table does for you, or how it's supposed to work

why aren't ratings just values? suppose you had a table of people, and you wanted to record their weights in kilograms -- would you create a Weights table and use a numeric surrogate key as the id? i wouldn't, i'd just record the weight values as values

I got your point... rating should be merge with article table.. isn't it?

depends


what does this mean --??

Thanks sir,
I am reading "Simply SQL" and big fan of yours. Thanks for reply..

Here is my data model.

Data Model.

My article rating is calculated using Country rating + Category rating + Site rating + keyword rating.

How I will easily do this without creating seperate rating table?

Please explain.

sorry, i don't understand your ratings

When I insert data in Article table I use stored procedure,
I calculate ArticleRating as follows,

ArticleRating = CountryRating (Country of news ) + SiteRating( News Website rating from where articles are comming from) + CategoryRating(Category of news on news website) +
KeywordRating (Rating of predefined keywords for particular news article )

My problem is all those ratings come from different tables.
CountryRating: From country table
SiteRating: From Site table
CategoryRating: From category table
KeywordRating: From keywords table

This approach is right or Should I create special Rating table where I store ratings of all above field ?

okay, that looks fine

you do not need a Ratings table for that

Thanks, This is what I wanted..