DB structure for a blog service

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

[Hadi]

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]

Any help?

[elitebomber]

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.