database - Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud

Question

We are working on rewriting our existing RIA and redesigning our database to re-architect it's design. Now we have 2 opinions about database:

(This choices are for SaaS based hosting.)

1) Individual database for each customer.

2) Single DB for all customers.

We are expecting good amount of data, some of our customers have db size ranging from 2GB to 10GB. # of tables are around 100.

Can I get an answer about which choice we shall go for?

We are not thinking about NoSQL solution as of now but we are planning to support about 4-5 databases with JPA (Java Persistence API) which includes MySQL, Postgres, Oracle, MSSQL for now.

P.S: We might leverage Amazon cloud for hosting.

Answer 1

The three main techniques that is usually applied to the database usage for this kind of a multi-tenant requirement is below. You have already specified some of them.

1. Separate databases for each tenant:
   • very high cost, easy to maintain/customize, easy to tune, easy to backup, easy to code to.
2. Shared database but different schema:
   • Low cost compared to (1), may encounter issues quickly with increased db size, easy to personalize per tenant, difficult to backup/restore per tenant, easy to code to.
3. Shared database Shared schema:
   • Low cost, load of one tenant will affect others, security and app development a challenge, difficult to personalize per tenant, difficult to restore/backup.

I think the above points hold good for hosting on premise or on cloud. If you see the number of tenants growing or the data getting bigger then 1) or 2) is better. I have used option 2) and have seen it helping development and maintenance.