Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    DB Design Help: Articles & Comments website

    Hi,

    I am working on a basic blog-style website with articles and comments. I'm aware that similar software already exists (drupal, joomla) but I prefer to roll my own in this case, and besides, it's how I learn. :-)

    Here are the basic table details:

    Table Name: users
    Purpose: Information about people with access to the system.
    Column Names:
    id
    name
    hashed_password
    email
    website
    last_login_date
    last_login_ip
    permissions


    Table Name: articles
    Purpose: Articles published by people in users table.
    Column Names:
    id
    title
    users_id
    date
    content


    Table Name: comments
    Purpose: Comments on an article published by the public and users.
    Column Names:
    id
    articles_id
    comments_authors_id
    date
    comment


    Table Name: comments_authors
    Purpose: Information about people who commented on an article.
    Column Names:
    id
    name
    email
    website


    Work flow:
    1.) A user, from the users table, publishes an article.
    2.) The article is written to the articles table.
    3.) John Doe, not a user, comments on the article.
    4.) John's personal info is written to comments_authors.
    5.) John's comment is written to the comments table.

    Sounds good so far. Now consider this…

    1.) A user, from the users table, comments on an article.
    2.) The user's personal info is written to comments_authors.
    3.) The user's comment is written to the comments table.


    The Problem:
    Now we have duplicate information about the user. :-(
    The user's name, email address, and website url, are stored in the users table and the comments_authors table.


    Is this an incorrect design? Is there a correct way instead?
    A system with only a few users, maybe not worrying about.
    A system with hundreds of users, well, that's a lot of duplicate data.

    I really appreciate your help. Thanks!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    You could just have the relationship from comments to users, user_id.
    Dave

  3. #3
    Join Date
    Nov 2010
    Posts
    2

    Still debating...

    Thanks for the recommendation!

    I'm still debating which approach to take so I listed some pro's and con's of each approach. Someone convince me! :-)

    Users and commenters all in the users table
    - Pro: one less table (comments_authors table removed)
    - Con: hashed_password, last_login_date, and permissions columns don't apply to commenters
    - Con: as table grows, query time increases when users want to log in

    Current design using comments_authors table
    - Pro: no unused columns in any table
    - Pro: quicker query time when users want to log in
    - Con: duplicate user data in comments_authors table (# users in users table = # additional rows in comments_authors)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nkline View Post
    - Con: hashed_password, last_login_date, and permissions columns don't apply to commenters
    that's what NULL is for


    Quote Originally Posted by nkline View Post
    - Con: as table grows, query time increases when users want to log in
    no, not at all, not if the table is properly indexed

    Quote Originally Posted by nkline View Post
    - Pro: quicker query time when users want to log in
    no, see above

    Quote Originally Posted by nkline View Post
    Con: duplicate user data in comments_authors table (# users in users table = # additional rows in comments_authors)
    yup, i see that as a major drawback

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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