(I am not sure if this is the best place to post this question. If not, please redirect me to the appropriate stack exchange site.)
I am looking to deploy a web application for multiple clients. The idea is that each client can only access his or hers own data. Let's say that the client is the owner of the application installation. Each installation can have multiple users. All installations will be in my own hosting, so that I can better control the code and business model (its a SaaS kind of thing).
I am looking for technical options to perform this kind of deployment.
[Option 1] - Shared single database
On easy option to do this would be:
- change the database in order for each relevant table to know its "owner id".
- change all SQL queries so that each User will only fetch data that is related to its "owner id" (I don't have the requirement for an User to be common to more than one client/installation).
However, this solution has a few drawbacks:
- requires changing most of the SQL code;
- there might be some performance impact since the database will have much more data (the data for all installations).
- mixing data of different clients in the same database may cause some issues (if there is a bug, its easier for someone to end up accessing some else's data);
So I would prefer an alternative where the data would be isolated.
[Option 2] - Multiple databases
One alternative that I was considering was:
- For each installation, buy a DB from the hosting company
- Come up with a configuration scheme that allows associating each installation with the database where the connection should be performed.
- For example, if the user accesses http://very-nice-app.com/client-3/ the code knows that it must connect to database-name-for-client-3.
This solves the potential issue of data leaking, but requires me to maintain several databases (if DB tables change, it has to be done across multiple installations). This can probably be automated somehow.
What would be the better approach? Is there a better alternative than the ones I have indicated?
Thanks in advance for your help.
- The database engine will probably be MySQL.
- This is a startup, so we are trying the business model. We don't have a good idea of how many clients we will have - it will be zero for some time. Right now we are looking for the ability to deploy and scale for a few clients (lets say up to 10).
- This app is targeted at small business and the amount of data should not be in millions of records. The largest table should have about 10.000 records per year (per client). This value is based on the company with whom we did the prototype (which is a mid sized company for the area x business).