Portal Home > Knowledgebase > Articles Database > Software As A Service One Database Or Many?

Software As A Service One Database Or Many?

Posted by JustinK101, 04-21-2008, 03:31 AM
I am creating a software as a service product and I am going back and forth on a database design issue. Should I create a separate database for each new customer, or create one big database and store all customers and do the standard relational database design idea. The clear choice for me is create a new database for each new customer, but the only problem is rolling out schema updates and bug fixes. If I have 5000 customers and then 5000 databases, how to manage creating a new table, editing a column name or data type, editing a stored procedure, or creating a new trigger on 5000 separate customer databases? So, there are many benefits for making a new database for each customer that I can think of: With separate databases, customer’s data are each separate entities, it is the whole encapsulation/modular design idea. It is impossible to get data out of a different customer databases. With one large database a query that forgets to include the WHERE clause selecting a customer_id can cause havoc. With one large database all customers are located in one, actually maybe a few, files. So many of the selects, inserts, deletes will scan all customers data, when all they ever need or care about is their own data. Even with indexes, inserts and deletes become slower with one large database as the size of the database increases. What happens if you have one large database with 100,000 customers and say a table with 30 million records? Then you need to spilt the database across multiple servers, a cluster, because one machine inst cutting it. With a single database design, cluttering becomes a headache. With multiple databases, no problem, just setup a new server and start adding new databases. Also, last thing is database integrity. With one large database if you database gets corrupt or accidentally deleted, or modified in a way which causes unwanted results all your customers are screwed. With multiple databases problems only effect the one single customer, again each customer is a separate entity. The only problem of course with multiple databases, and the root of my question; how do I manage database schema maintenance? How do I effectively roll out changes to each customer database schema? Thanks.

Posted by andrewtayloruk, 04-21-2008, 04:50 AM
Just develop a management tool that can work on multiple databases; a basic : foreach(database in showDatabases()) alter table ... or create_stored_proc It's all about advantages / disadvantges and whatever will cause you pain using this method is a lot easier to handle than the problems one large database could cause. Also, think about the future, what if you have a customer who wants to run a single instance of your application on an internal network, you would want to be able to capitalise on that market very quickly. Andrew

Posted by wKkaY, 04-21-2008, 06:01 AM
I suppose for each database, you could have a table that stores the schema version. Then when you update the schema, perform the schema updates and version table update in a single transaction. At least you know that your database is all in one version or another. PS: I've looked at the MySQL docs and it turns out that InnoDB isn't transaction safe for ALTER TABLE, so you can't actually use this Postgres's seems to be though.

Posted by JustinK101, 04-21-2008, 11:45 AM
wKkaY: What do you mean by transaction safe? By the way, yeah I am using MySQL 5 with INNODB tables.

Posted by Steve_Arm, 04-21-2008, 11:56 AM
By no means don't do a one database for all strategy. The answer is simple, create an update plan for the software. Hint: if you don't want to break everything when you will add an update that modifies the database, think before hand. Never do a SELECT *. Select specific fields.

Posted by RBBOT, 04-21-2008, 02:19 PM
Most applications designed to scale in this manner have a single master database of all the customers, and a number of content databases, each of which holds the content for a fixed number of customers - say 1000 each. The master database contains account information and details of what content is stored in which database. That way you don't end up with a huge monolithic database, but you limit the number of databases you need to apply schema updates to. Microsoft's SharePoint server is an example of an application that does this to scale.

Posted by Panopta, 04-21-2008, 02:41 PM
Lots of good points in favor of separate databases have been raised so far. A few other things to consider when designing something like this: Will you'll need to have any internal/administrative interface that needs to function across customers - for example any CRM-type functionality where your support staff need to search across all customer's data. If you do, that gets a lot harder to do if each customer is segregated into different databases. Will you have the potential of merging customers? I've run into this in the past when customers go through acquisitions, or setup separate test accounts that turned into production use somewhere down the road. Having to do this with separate databases turns out to be a nightmare of updating primary keys to avoid collisions, whereas it can be much simpler if everything is in one database. This is one of those cases where there is no one "right" solution - it really depends on the specifics of your application. But definitely think through it carefully before proceeding, as this is the kind of thing that is really painful to change down the road.

Was this answer helpful?

Add to Favourites Add to Favourites    Print this Article Print this Article

Also Read
Dathorn (Views: 274)