SAAS and Multi-tenancy in Symfony2?

Question

I've been using Symfony for close to 2 years now, so far, each project I build is deployed specifically for each client (i.e one client, one codebase, one db).

Lets say I have a Project Management app that I want to deploy for many clients. Assuming the clients will go with whatever features I build into the system, if I deploy a different codebase (therefore, a different db) for each client, here are the problems I foresee:

1. Pushing bug fixes and upgrades will be painful. I need to push it to every repository that I have deployed. It won't scale well if I have 50 clients using that same app.

2. Management is painful. How do I build an admin system for myself where I can pull ALL the projects into one HTML table? After all, each client has their own database, right? For me to do anything meaningful with all the records of all my clients, I need a way to look through all their databases at one go, which.. I don't think Symfony allows. (I'm not sure)

3. User account issues. If a user happens to work for multiple companies, all of them using my Project Management app, that user has to sign up multiple times. (I know this can be circumvented if I use oauth, but I'm trying not to go there if I can)

Here are the solutions I have thought up and tried to a certain extent.

---

Solution 1

One database and one codebase for ALL my clients. Projects will go under one table, Invoices go under one table, all marked by their own client_id. Users can be assigned to Projects so there is no need to sign up multiple times.

This is not that hard to create. But, what happens if different clients need different columns for their Invoices? My Invoice table will keep expanding (with different fields that different clients want), and each row can potentially contain many null fields. Not to mention, my Invoice entity will grow in file size, and I will have to update the database schema every time a new customization comes in.

---

Solution 2

One database where each client has their own table prefix. So for Client A, I could use clientA_projects, clientA_invoices, clientA_configuration etc.

This is ideal if each client wants to customize their fields. But, does this mean I need to create new entity and form classes for each new client that comes into the system? It looks like with this solution, I need to update the database schema with every new client I get.

---

Currently, I am experimenting with schema-less databases (mongo and couch), hoping that without needing to specify the table schema upfront, I can implement Solution 1 without hassle. But I'm still experimenting, and there are ways to go before I dare to deploy a production-ready app, being unfamiliar with the issues of mongo and couch with Symfony.

---

So, this is where I am stuck at. Being a self-trained programmer, I feel I have a lot of holes in my knowledge that requires filling (as opposed to someone from a CS background). There aren't many places on the web talking about Symfony 2 and multi-tenancy (maybe I'm looking for the wrong thing). If anyone of can point me to a clearer direction, maybe best practices, example projects, I will really appreciate it!

Btw, I plan to execute this in the latest version of Symfony (2.3.2 at this moment).

Thanks in advance guys.

Answer 1

I'm also using Symfony2 for similar amount of time (since one of BETAs) and I advise you to go with solution #1. If you are going SaaS you can't give out code to clients for the reasons you wrote (problems with updates / upgrades mainly). The whole hassle will be on the user management - which user has access to which data, belongs to which group, company and so on. All other things if done properly will be coded the same user-agnostic way. What should you do with different requirements for different companies? Make such features configurable. You can implement this on various levels:

• simple entity attributes: have an attributes field in each table and save everything as JSON, YAML or other dynamic-structurable content,
• general configuration: have one place where entity base configuration is stored (in the means I wrote above) and allow users to manage new features from there, all changes are propagated to simple entities,
• implement something what I call Entity Parameters Pattern - design database tables which would contain parameter types, parameter values and relation to other entities on different levels and then make generic configurable parameter types which can be applied to any place with predefined meaning. For example "preferred_season" being a parameter of type "choice_string" containing configuration "spring,summer,autumn,winter" and when attached to given entity would always render a <select> field with choices and save selected value with relation to both entity and parameter type.

Also the solution #1 has one unbeatable advantage - it could handle more that one company even if you wanted to give out the code at the end. You'd just need to mask the ability to add more. :)

This question is tagged Symfony2 but it really shouldn't. It doesn't matter what framework you're using, you should abstract your application design from code and then use frameworks as a mere tool for doing the job smoothly. I'd like to say that even taking previous sentence into account, I'm absolutely in love with Symfony2. :)

Answer 2

I know this is an older question but can be usefull for others.

I agree with @Tomasz and solution #1 with one database – all tenants in one database. The biggest problem here is proper database design to solve further security issues: access for resources must be controlled by application to prevent unauthorized access between tenants. On the other side we have ease implementation as we are implementing single application with only one database.

Nice article about Symfony2 and moving to SaaS model: http://www.browserlondon.com/blog/2015/01/moving-to-a-saas-model-with-symfony2/

Also "must read" article about designing database in SaaS platform - patterns that are platform independent: http://labs.octivi.com/database-design-in-saas-platforms/