DB Design Help: Articles & Comments website

[nick1]

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

[seanlim]

i don't think there is one, correct way of designing your DB, just a matter of optimization to reduce redundancies and usage of system resources.

 

from what i can see, your comments_authors table is redundant as it can be merged with the users table. however, you will have to make distinction between "registered" users and public users who are only in the table because of the comments they post. a possible solution is to use the permissions field, or even the hashed_password field.

 

by merging the two tables the following issues are solved:

1. information of registered users who post will not be duplicated on a separate table

2. public users who comment can, at a later time register and their comments will still be linked to their account