DB Design Help: Articles & Comments Website

Question

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 such 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!

Answer 1

I appreciate the feedback and different points of view. 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 table)

Answer 2

People harp on duplicate data... and it can be .. CAN BE a problem. If you were building order-entry systems, accounting, trading, system... it makes a big deal.

This is for BLOG COMMENTS... what's the worst that can happen?

at worst you've got two copies and only two copies of posters... every schmuck who only comments is there just once. So if you had 1000 posters, you'd have 1000 extra rows of about 100 bytes rows... whoa, you're chewing up an entire MEGAbyte of database space.

Am I recommending this datamodel as a pattern for emulation, no. I'd design this a lot differently. But I design for very large scale enterprise applications and those designs aren't necessarily the easiest thing to program against. They just perform great. But in your case, start coding and don't sweat that little redundancy.

Answer 3

Maybe I'm not understanding your design very well, but wouldn't it be better to remove the comments_authors table and use the permissions field in your users table to tell whether the user is allowed to publish articles or just add comments.

Answer 4

For every new comment, you could create a new 'user' with public privs and no password. Make the email address the unique username and you won't have duplicate data. On comments, you could then make comments_authors_id user_id instead.