SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    storing website articles question (article format)

    Hi guys,

    I'm developing a simple project to store articles for an informational health-related website. I need to store a few different types of article. For example, one type of article may just be a simple "Why fruit and vegetables are good for you" article with just textual content, but another might be a review/recommendation of, say, a Vitamin C product which would have a 'Description' section and a 'Suggested dosage' section.

    The articles would render something along the following lines:

    Code HTML4Strict:
    <!-- simple article -->
    <div class="article">
    <div class="author">Bob</div>
    <div class="article_body">Fruit and veg are good for you... etc</div>
    </div> 
    </div>
     
    <!-- more complex article -->
    <div class="article">
    <div class="author">Bill</div>
    <div class="article_body">
    <div class="product_description">500mg vitamin c tablets from citrus..</div>
    <div class="suggested_dose">1 tablet per day with a meal</div>
    </div>
    </div> 
    </div>

    So the articles would have common elements such as its author (but also title, date of posting, number of views etc) but some article specific elements such as for the 'product description' articles, a description of the product element plus a suggested dose element.

    I was thinking of storing all articles like so:
    Code SQL:
    author | DATE | content | TYPE (ADD other COLUMNS AS necessary)

    Where content was the article content in XML. So a sample record would be:
    Code SQL:
    Bob | 08/08/2008 | <description>Vitamin C tablets</description><dose>1 pill per day</dose> | product_description

    That way I can store all the articles in one table and still differentiate between different types of article using the 'type' column and then the website could render the article with the appropriate divs for that type of article.

    Is this the best way to go about this? Any help would be appreciated.

    ps - The target database would be MySQL most likely, so I cannot use OO-databases such as DB2..

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If there will only be a few extra columns, I'd keep it all in one table, fastest and least amount of work for you and the server. A few sometimes-NULL columns won't add much to query time or disk usage.

    If there are a lot of extra fields, and articles on average use few of them, you could create a second table... article_id | field_type | field_data... and do a second SELECT from that metadata table when you show the article.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i agree with dan, have separate, additional, optional columns

    seems like from your example you want to place multiple values into a single column using xml encoding -- i would use separate columns for each
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, but what if in future I want to add another type of article? won't that involve adding even more columns?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by old_iron View Post
    won't that involve adding even more columns?
    yes, but how often are you going to do it?

    okay, you have an informational health-related website, with articles that have specific columns author and article_body, as well as some additional columns such as description and suggested dosage...

    and of course you have these additional columns because you want them to be separate from the main body of the article (where of course you can talk about description and suggested dosage in great detail), so this would imply that you want to search on these separate columns (e.g. find all articles where the suggested dosage is 81 mg/day)...

    can you think of any other columns like this that you might want to search on? and if you can't, now, think of any, that's okay, because you'll have plenty of time



    how long will it take to build the front-end user-facing workflows and screens to deal with searching new columns (to say nothing of the admin screens required)?

    what would be wrong with adding these new columns when the time comes, after you've wireframed the functionality that these new columns will support?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast webburu's Avatar
    Join Date
    Mar 2008
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you need to expand the table by fields without changing the database interface in your application I think you will need EAV data model you can go here to take a research: http://weblogs.sqlteam.com/davidm/articles/12117.aspx


Bookmarks

Posting Permissions

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