-2

We are in the planning stages for a new multi-tenant SaaS app and have hit a deciding point. When designing a multi-tenant application, is it better to go for one monolithic database that holds all customer data (Using a 'customer_id' column) or is it better to have an independent database per customer? Regardless of the database decisions, all tenants will run off of the same codebase.

It seems to me that having separate databases makes backups / restorations MUCH easier, but at the cost of increased complexity in development and upgrades (Much easier to upgrade 1 database vs 500). It also is easier / possible to split individual customers off to separate dedicated servers if the situation warrants the move. At the same time, aggregating data becomes much more difficult when trying to get a broad overview of how customers are using the software.

We expect to have less than 250 customers for at least a year after launch, but they will be large customers and more will follow afterward.

As this is our first leap into SaaS, we are definitely looking to do this right from the start.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Kevin
  • 512
  • 4
  • 15
  • I added the [tag:multi-tenant] tag for you. You might want to click it and read through some of the questions and answers. [Here's one of mine](http://stackoverflow.com/a/8343142/562459), and [an article from Microsoft](https://msdn.microsoft.com/en-us/library/aa479086.aspx). – Mike Sherrill 'Cat Recall' Apr 14 '15 at 00:59

1 Answers1

3

This is a bit long for a comment.

In most cases, you want one database with a separate customer id column in the appropriate tables. This makes it much easier to maintain the application. For instance, it much easier to replace a stored procedure in one database than in 250 databases.

In terms of scalability, there is probably no issue. If you really wanted to, you could partition your tables by client.

There are some reasons why you would want a separate database per client:

  • Access control: maintaining access control at the database level is much easier than at the row level.
  • Customization: customizing the software for a client is much easier if you can just work in a single environment.
  • Performance bottlenecks: if the data is really large and/or there are really large numbers of transactions on the system, it might be simpler (and cheaper) to distribute databases on different servers rather than maintain a humongous database.

However, I think the default should be one database because of maintainability and consistency.

By the way, as for backup and restore. If a client requires this functionality, you will probably want to write custom scripts anyway. Although you could use the database-level backup and restore, you might have some particular needs, such as maintaining consistency with data not stored in the database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm up voting this answer as it does outline the differences between both use cases. I'd personally opt for the one database solution, purely because of the management aspect involved with multiple databases. – Darren Apr 14 '15 at 00:46