1

We have developed a small ERP application and the architecture right now is a 1-1 deployment/codebase for each client.

We are now trying to convert it to a SaaS solution and we have the following approaches possible

  1. Separate DB for each client

    Pros

    • Easier conversion of the existing applications
    • Data is isolated

    Cons

    • Harder to maintain and update as we grow
  2. Single DB for all clients with client ID in all tables

    Pros

    • Easier to update

    Cons

    • Possible data leaks(?)
    • Huge database

What's the best practice? For reference we are using Symfony2 with MySQL.

  • Does your database support row level security? In that case it might be possible to use a separate database user for each tenant and automatically filter the rows by tenant_id using your second approach. (Disclaimer: I have never used this approach myself, so I don't know how well it works in practice) – CodesInChaos Feb 24 '16 at 8:49
  • 1
    The first approach has another advantage: You can scale out to multiple servers without changing the design, as long as no tenant needs to be split across multiple servers. – CodesInChaos Feb 24 '16 at 8:56
  • Another variant is using the second design, but creating filtered views for each client. – CodesInChaos Feb 24 '16 at 8:58
  • This is what i'm thinking. Scaling and maintenance would be much easier as we'll only be thinking about one database. My big issue really is data security. I don't have enough experience with that setup to know the pitfalls. – wnoveno Feb 24 '16 at 9:36
  • Maintenance would be easier with just one database, but scaling not so much. Because with separate databases, you can distribute the separate databases across multiple hosts without having to make any of them distributed. – Jan Hudec Feb 24 '16 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.