Best database model for saas application (1 db per account VS 1 db for everyone)

Question

Little question, I'm developing a saas software (erp).

I designed it with 1 database per account for these reasons :

• I make a lot of personalisation, and need to add specific table columns for each account.
• Easier to manage db backup (and reload data !)
• Less risky : sometimes I need to run SQL queries on a table, in case of an error with bad query (update / delete...), only one customer is affected instead of all of them.

Bas point : I'm turning to have hundreds of databases...

I'm hiring a company to manage my servers, and they said that it's better to have only one database, with a few tables, and put all data in the same tables with column as id_account. I'm very very surprised by these words, so I'm wondering... what are your ideas ?

Thanks !

Frederic

Answer 1

The current environment I am working in, we handle millions of records from numerous clients. Our solution is to use Schema to segregate each individual client. A schema allows you to partition your clients into separate virtual databases while inside a single db. Each schema will have an exact copy of the tables from your application.

The upside:

• Segregated client data
• data from a single client can be easily backed up, exported or deleted
• Programming is still the same, but you have to select the schema before db calls
• Moving clients to another db or standalone server is a lot easier
• adding specific tables per client is easier (see below)
• single instance of the database running
• tuning the db affects all tenants

The downside:

• Unless you manage your shared schema properly, you may duplicate data
• Migrations are repeated for every schema
• You have to remember to select the schema before db calls
• hard pressed to add many negatives... I guess I may be biased.

Adding Specific Tables: Why would you add client specific tables if this is SAAS and not custom software? Better to use a Postgres DB with a Hstore field and store as much searchable data as you like.

Schemas are ideal for multi-tenant databases Link Link

A lot of what I am telling you depends on your software stack, the capabilities of your developers and the backend db you selected (all of which you neglected to mention)

Your hardware guys should not decide your software architecture. If they do, you are likely shooting yourself in the leg before you even get out of the gate. Get a good senior software architect, the grief they will save you, will likely save your business.

I hope this helps...

Bonne Chance