Separating Data in Different Realms With Doctrine2

First of all and before getting started, today has been declared to be #bloggerlove day by @caefer over there so be sure to leave comments on the blogs you like and read. As he says, bloggers make our lives easier and share a lot of expertise in many different domains. It’s always great when you’ve spent hours researching on something and put some more time to try and explain it to receive a simple ‘thanks’. Or if you don’t want to comment here, you can always send a postcard to @fabpot! Ok, now on to today’s problem!

The software I’m working on right now will be a sort of SaaS (think SalesForce or BaseCamp). So on a single application, I will (hopefully) have many different companies log on and use it.

Of course you can NEVER allow users from company A to see anything from company B. So you need to separate all of their data. One way to do so would be to create a real new database for each new company using the software. Then when a user logs on, you can set the needed doctrine connection to the corresponding database. I’m not sure how this would actually work and foresee more troubles by doing so. However any feedback is welcome if you’ve done so and think this is the way to go, I’m just trying to think it through for now.

The other solution requires that on each table of your database you add a ‘client_company’ field for example. This way you know exactly which persisted entity belongs to which client and you can select those appropriately to only display contracts saved by company A to users from company A.

Doing so manually seems like a lot of tedious work. First you’ll have to set the company field on each entity creation, then for each subsequent select, you need to add a WHERE clause to your request. So I went over to the Doctrine documentation to see if there was a way to do things more simply. And there … half is!

Doctrine will create many entity lifecycle events. Namely when persisting a new entity, updating it or deleting it. You’ll have an event sent before and after each of these. So adding the company field through an event listener will be quite easy. Listen for prePersist event, set the company field to the company of the currently logged in user, put everything back in the pipes and you’re done. I haven’t checked completely yet, but with Doctrine inheritance, you might even be able to create a base ClientPrivateEntity class to be extended by the others in order to only set this behavior once.

The other part you’ll need is to filter all of your queries per company. Sadly, there is nothing available yet in Doctrine2 for this kind of matter. It seems to be planned for the future, but for now, you’re screwed on this part and will have to do everything manually.

If you have any thoughts on how such separation of data is possible and should be achieved, I’d happily hear from you. I don’t think the multiple databases is such a good solution though I don’t have many arguments against it right now (except the pain it would be to make changes to 2000 databases when deploying a new version). And requiring to add the WHERE clause on each and every query seems a bit dangerous. Too easy to forget. I’d much rather have it done in a central place once and for all if it were possible.