Multi Organization SaaS mySQL Setup?

Question

I'm part of a team currently developing a dedicated SaaS service for a specific crowd of organizations. I have knowledge in PHP and mySQL, so I am developing it on these platforms. Will be deploying this on the cloud and releasing in multiple countries.

Ive come to the point of separating organizations/main-users in the database and wanted to see what you guys think.

When the SaaS manages invoices and many other sensitive information what would be the best process of distributing it on the mySQL server? Ive thought of the below options:

1) having all information in a single database in single tables and separated by a organization identifying row. - does seem secure and may be slow when there are a few thousand users and 10,000 rows?

2) having a single database but separating tables with a user id eg. '1000_invoices' - again may be faster but not as secure.

3) have separate databases created on each organization signup and a specific user used to access the database and the database name is stored in the sessions/cookie? per organizations users.

Anyway i was wondering what you guys think will be the best option? and if not the above then what do you recommend? and why? also anything regarding security will be greatly appreciated. Have not worked with large multi-organization applications before. thanks in advance!

Answer 1

I've developed numerous SaaS applications in the past and we've found the "single application deployment, single datatabase" setup as used by large "public" SaaS services (like KashFlow, possibly Salesforce?) didn't make much sense. Here's why:

• Number 1: Client companies with confidential information are going to want assurances their data is more "secure" and it's easier to make these promises when their data is partitioned beyond the application tier.
• Different clients sometimes want their software application customized to them, such as their own extra database fields, a different login screen visual design, or different (or custom) system "modules" - having different application instances makes this possible
• It also makes scaling easier, at least when beginning. It's easier to load-balance by provisioning a single client's application to its own server separate from the others, whereas with a single application it means you need to spend longer developing it to make it scalable.
• Database primary keys are easier to work with. Your customers might start asking questions such as why their "CustomerID" values increment by 500 each time instead of by 1.
• If you've got customers located around the world, it's easier to provision a deployment in another country (with its own local server and DB server) rather than deploying a giant application abroad or forcing users to use intercontinential (i.e. slow+laggy) connections to your servers located thousands of miles away.

There are downsides, such as the extra administrative burden of managing hundreds, possibly thousands of databases, in addition to application software deployments, but the vast, vast simplifications it makes to the program code make it worthwhile. Besides, automating provisioning and deployment is easy with a bunch of shell-scripts.