DB structure for a blog service

Forum Moderators: open

Message Too Old, No Replies

DB structure for a blog service

should we have a db for each user or just a new table

Hadi

5:55 pm on Apr 17, 2008 (gmt 0)

Hello

We are developing a hosted blog service.

We are going to have up to 100,000 users/blogs or more.

Now for our DB structure considering performance and speed, we are not sure to create a new table for each user or to create a new database for each new user.

I appreciate any help in advance

Hadi

11:48 am on Apr 23, 2008 (gmt 0)

Any help?

elitebomber

7:03 pm on Apr 23, 2008 (gmt 0)

You definitely want it all in the same database. Making a separate a table for each user is not a good design. Assuming a really simple blog structure, I would have three tables. One for users and one for blog posts and one for comments. I would use MyISAM (default engine) over InnoDB for performance purposes.

Users table:
user_id (key)
fname
lname
email
favorite_color

Blog table:
post_id (key)
user_id (index this)
date_of_post
content

Comments table:
comment_id (key)
post_id (index this)
user_id (this is referring to the user_id of the person commenting. It's not required if non-users can comment)
comment_content

That's a pretty straight forward approach to a blog.