DB Design Help: Articles & Comments Website

Databases
1

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. :slight_smile:

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. :frowning:
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!

2

i would combine them, and have just a single persons table

:slight_smile:

3

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! :slight_smile:

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

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