Should I use one or more database settings for a multi-tenant application?

Question

I am working on a PHP application that intends to simplify the workflow and project management, say something like Basecamp and GoPlan .

I am not sure what the best database based approach is. Should I use a single database and add columns for specific clients in each of the tables or create a database for each new client? An important factor is automation: I want it to be dead just to create a new client (and, perhaps, opening the opportunity to register for myself).

Possible cons I can think of using a single database:

• Lack of extensibility
• Security issues (although there shouldn’t be any errors first)

What do you think about it? Do you have any ideas on which solution the above companies have chosen?

Answer 1

I usually add ClientID to all tables and go with the same database. But since databases are usually difficult to scale, I also let you run different database instances for some or all of the clients.

Thus, you can have many small clients in one database and large clients on different servers.

A key support factor, though, is that you keep the schema the same across all databases. There will be enough headache for version control without introducing customer-specific schemes.

Answer 2

Listen to the Stackoverflow podcast, where Joel and Jeff share the same issue. Joel talks about their experience offering a hosted version of their software. He points out that adding client identifiers throughout your database complicates the design and code (are you sure you didn’t accidentally forget to add it to any WHERE clause?) And complicates the hosting function, such as backups for a particular client.

This was in episode No. 20 or No. 21 (for more details see transcript).

Answer 3

In my opinion, this will depend on your likely customer base. If you could get into a situation where arch-rivals use your system, then you will be better off with separate databases. It also depends on how your databases are implemented by multiple databases. If each database has a separate copy of the infrastructure, then this assumes a single database (or a change in the DBMS). If several databases can be served by one copy of the infrastructure, I would go to separate databases.

Consider backing up a database. Client A says: "Please send me a copy of my data." Much easier to set up a database separately than when sharing a single database. Consider uninstalling a client; again, much easier with separate databases.

(The “infrastructure” part is muddy because there are significant differences between different DBMSs regarding what constitutes a “database” compared to a “server instance.” Add: the question is marked as “mysql”, so maybe these thoughts are not quite relevant.)

Add: Another problem - with multiple clients in the same database, each SQL query will have to provide data selection for the right client. This means that SQL will be more difficult to write and read, and the DBMS will have to work more on data processing, and the indexes will be more, and ... I really will go with a separate database for each client for many purposes.

Obviously, StackOverflow (as an example) does not have a separate database for each user; we all use the same database. But if you use accounting systems for different companies, I don’t think it would be acceptable (for companies and, possibly, not for legal entities) to share databases.

Answer 4

• DEVELOPMENT For quick development, use the database for each client. Think about how easy it will be to backup, restore, or delete client data. Or to measure / monitor / use the account. You do not need to write code to do this yourself, just use database primitives.

• PERFORMANCE For performance, use the database for everyone. Think of a connection pool, shared memory, caching, etc.

• BUSINESS If your business plan should have many small clients (think hotmail), you probably should work with one database. And all administrative tasks, such as registration, deletion, data migration, etc., are fully automated and accessible in a user-friendly interface. If you plan to have dozens or up to several hundred large clients, you can work in one database for each client and have system administration scripts that can be serviced by your customer support staff.

Answer 5

The following screencast explains how this is done on salesforce.com. They use one database with a special column OrgId, which identifies the data of each tenant. There is much more, so you should study this. I would go with them.

There's another great article on this on MSDN. It explains in detail when you should use a general or isolated approach. Remember that having a common database for all your tenants has some important security implications, and if they all use the same database objects, you can use [row-level security] - depending on the DBMS used (I'm sure that possibly in MS SQL Server and Oracle, possibly also in IBM DB2). You can use tricks such as row level security in mySQL to achieve similar results (views + triggers).

Answer 6

For multi-routine, performance usually increases the amount of resources that you have for each other, see

http://en.wikipedia.org/wiki/Multitenancy

So, if you can, go to a single database. I agree that security problems will arise only due to errors, since you can implement all access control in the application. In some databases, you can still use database access control by carefully using views (so that each authenticated user gets a different view).

There are also ways to provide extensibility. For example, you can create a single table with extension attributes (with a key by identifier, base record and extension attribute identifier). Or you can create extension tables for tenants so that each tenant has their own extension scheme.

Answer 7

When you design a database with multiple tenants, you usually have three options:

• Have one database for each tenant
• There is one scheme for each tenant.
• All tenants have the same table.

Your choice has implications for scalability, extensibility, and isolation. These implications have been widely discussed in various StackOverflow issues and in database articles.

In practice, each of the three design options - with sufficient effort - can solve issues around scale, data that varies between tenants and isolation. The decision depends on the main aspect for which you are building. Summary:

• If you create for a scale: do all tenants use the same table (s)
• If you are building for isolation: create one database for each tenant

For example, Google and Salesforce follow the first pattern, and their tenants use the same tables. Stackoverflow, on the other hand, follows the second pattern and saves one database for each tenant. The second approach is also more common in regulated industries such as healthcare.

The solution comes down to the main dimension for which you are optimizing your database design. This article on developing your SaaS database for scale talks about trade-offs and provides a summary in the context of PostgreSQL.

Answer 8

Another point to keep in mind is that you may have a legal obligation to store data from one company separately from others.

Answer 9

Having a database on the client is usually not sufficiently scaled. MySQL (and, possibly, other databases) contains resources open for each table, this does not allow a good approach to 10k + tables on one instance, which can happen in a large-scale multi-user situation.

Of course, if you have another problem that causes other problems before you reach this level, this may not be relevant.

In addition, fragments of an application with multiple tenants are likely to be correct in the end, as your application gets bigger and bigger.

Sharding, however, does not mean one database (or instance) for each tenant, but one per fragment or set of fragments that several tenants each may have. You will need to find the right settings for yourself, possibly in production (hence, it should probably be completely tunable from the start)

€ I can not guarantee this.

Answer 10

You can start with a single database and break it down as your application grows. If you do this, I would recommend a few things:

1) Create a database so that it can be easily shared. For example, if clients are going to exchange data, make sure that the data is easily replicated in each database.

2) If you have only one database, make sure that it is copied to another physical server. If you switch to another resource, you can return traffic to this other server and still have your data.