one database or multiple databases

Question

Possible Duplicates:
What is better: to have many similar databases or one database with similar tables or one database with one table?
Database per application VS One big database for all applications

SaaS Application with many users. One database for each user's information tables and one central database, one database containing identical tables for each user, or one database with one set of tables for all the user's information?

Thanks, Brandon

Answer 1

As usual, it depends. There are a few considerations here... How many users do you intend to support (100 or 1 million) and how much data will they have? Also, will you need to perform any queries that consider more than one user at a time?

In terms of scalability, being able to horizontally partition your data is a good (well, great) thing. It's much easier to scale a solution that is already split up at the database level.

So some basic guidance (in the absence of more detail):

• If we're talking about relatively small amounts of data that isn't going to grow very much, just stick everything in one database.
• If there is a significant amount of data per user and not many users, it might be advantageous to create a database per user.
• A more flexible approach might be to create a database per range of user. This is similar to the MySpace approach where each server contains a million users.

Keep in mind if you make the user part of your architecture scale, but are still constrained to a shared central database, that central database becomes the bottleneck.

Oh - I'll use this to make my usual plug for using Utc for storing dates. Start out this way now and your life will be much easier if your app grows across multiple time zones.

Answer 2

The one database per user solution just wont scale!

Say your system needs 20 tables, then you start hitting magic (or more correctly cursed) numbers like 32760 with only 1638 users. Plus most admin tools will slow to a crawl when your system contains thousands of tables, indexes etc. And you backup and maintenance becomes a full time job.

To use a single database you only need to add one extra "owner" column in each table and index.

Also depending on what your "SaaS" service actually is I doubt you will end up with a simple "one user uses only one set of data, and, one set of data is used only by one user".

Think of familys and small companies where access to the system is shared by several users. OK they could just share the user id but they would probably like some sort of audit trail as to who exactly did what, and, perhaps different levels of access to the data.

You also have the opposite sistuation where a single user may want to adminster several sets of data (say a church member adminstering a set of data for the sunday school, the soup kitchen and the roof repiar fund).

The normal practice is to associate the data with a "contract" and to give individual users various levels of access to the contract or contracts.