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!

You could just have the relationship from comments to users, user_id.
Dave

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)

that's what NULL is for


no, not at all, not if the table is properly indexed

no, see above

yup, i see that as a major drawback