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:
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
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 ?