One schema for all or separation of Schema for each customer?

Question

Lets say you build an application which needs to serve several customers (data is never shared between customers) and the customer data itself is sensitive.

How would you design the database to prevent that suddenly (e.g. a bug) data from one customer is visible to another?

E.g. We have a projects table which contains projects of a customer. Now we could chuck all projects (of all customers) into that table or create a schema for each customer.

I like the idea of schema separation (cause it would totally separate the data) but as I never did it i am not sure if this is a good approach (e.g. change of the schema would require the maintenance of all customer schemes).

Important: The application contains master data which is shared across all customers (e.g. customer accounts, settings, templates, etc.) as well. So another disadvantage what I expect would be the maintenance of multiple connections at the same time...

UPDATE: Would it be possible to replicate just the schema automatically?

Answer 1

I think that your idea of separating every customer out would be the best option. Your master data could be kept by itself and would be ok as long as Clients do not access that table (or at least access it often). The key would in the separation of all the Client's sensitive data, agreed that this would cause more work. But hey, that's what you get for expanding your business.

Answer 2

With your requirements, separate customer DB schema's sound like a good idea. It'll be a bit of a headache, but not as much as a lawsuit.

Answer 3

You could instead create a single master table and have one view per customer. All queries would then go through this view, which would filter out everything outside of the relevant customer.

This makes maintenance easy since the views only define a single constraint (on the customer) and do not contain any other schema information.

Here's an example in MySQL (not the best DB for views, but hey)

CREATE VIEW projects.foocust AS SELECT * FROM projects WHERE customer = 'foocust';

Answer 4

My advice would be to separate the data for each customer as much as possible. Using SQL Server I would store each customer's data in a separate database (you can have many separate databases in an instance of SQL Server). If you don't have that option, a schema could do something very similar.

Answer 5

To properly weight the option I think you need to analyze the quality of your application code. I have built many systems for huge customers that shared databases just fine where we never had any concern about cross-sharing data. If you have high quality code where the authorization logic is secure, then I would always use one schema; maintenance is so much easier that way

On the flip side. If you have cobbled together asp pages with no business object library with no standard and centralized authorization logic; then certainly use different databases with unique logins per customer.