Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    12

    Database Design Question

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    12
    I got your point... rating should be merge with article table.. isn't it?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalrudra View Post
    isn't it?
    depends


    what does this mean --
    Final Rating is calculated using other factors like, rating of a country, rating of particular category on news source.. etc..
    ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    12
    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.
    Attached Thumbnails Attached Thumbnails NewDBDesign.png  

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalrudra View Post
    Please explain.
    sorry, i don't understand your ratings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    12
    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 ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalrudra View Post
    When I insert data in Article table I use stored procedure,
    I calculate ArticleRating as follows
    okay, that looks fine

    you do not need a Ratings table for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2009
    Posts
    12
    Thanks, This is what I wanted..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •