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

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.