Database Design for multiple users site

Question

Hi,

I am required to work on a php project that requires the database to cater to multiple users. Generally, the idea is similar to what they have for carbonmade or basecamp, or even wordpress mu. They cater to multiple users, whom are also owners of their accounts. And if they were to cancel/terminate their account, anything on the pages/database would be removed.

I am not quite sure how should I design the database? Should it be:

• separate tables for individual user account
• separate databases for individual user account
• or otherwise?

Kindly advise me for the best approach to this issue. Thank you very much.

Answer 1

How many users are we talking about?

Offhand, I like the idea of having a separate database for each user account. There are many advantages:

• You can keep the schema (and your application code) simple
• If a user ever wanted a copy of their database you could just dump it out and give it to them
• You can easily take care of security by restricting access to each database to a given user account
• You may be able to scale out more easily by adding more database servers, since you are using separate databases (there would be no common tables used by all users)

Of course, this could be a bit painful for you if you need to deploy updates to hundreds of databases, but that's what automated scripting is for.

The idea of having separate tables for each user seems like a coding nightmare. Each time you reference a shared table you will have to modify the name to match the current user's copy.