1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Frederic
  • 145
  • 2
  • 11

1 Answers1

4

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

Community
  • 1
  • 1
Sina Khelil
  • 2,001
  • 1
  • 18
  • 27
  • Sorry, i forgot to mention, i'm using MySQL. I looked for schema (i didn't know anything about that...), and it looks like schema and database are the exact same things in mysql (http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql). So might not be the solution... (or that may meen that my architecture is good haha) When i say i'm developping saas, i mean "software as a service", not "one standard with no custom fits all". I actually have a standard soft for every customer, but i custom it for every one also... that's why i need specific tables and collumns – Frederic Jul 03 '15 at 05:57
  • @Frederic I gave up on MySQL the moment Oracle acquired them. They have no incentive to innovate beyond their flagship product. Postgres on the other hand, has been introducing new features and capabilities regularly. Might I suggest a switch to Postgres, you will be better off in the long run. – Sina Khelil Jul 03 '15 at 14:35
  • 1
    "Get a good senior software architect, the grief they will save you, will likely save your business." - sage advice. – ViniH Sep 03 '18 at 10:53