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:
Table Name: articles Purpose: Articles published by people in users table. Column Names:
Table Name: comments Purpose: Comments on an article published by the public and users. Column Names:
Table Name: comments_authors Purpose: Information about people who commented on an article. Column Names:
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.
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'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)