How to Design a Database for Both Flexibility and Data Isolation?

Question

I am designing a system that serves multiple customers, providing data visualization for revenue and membership information.

Currently I use a separate schema approach to isolate customer data. Each customer has their own schema with nearly identical tables and columns, and the system determines which schema to connect to based on the customer name. While this ensures strong data isolation, it becomes a maintenance challenge: whenever there’s a change in requirements that affects table columns, I need to alter the same change across N tables for N customers. This makes the system less flexible.

I’m considering switching to a shared schema design. In this setup, all customer data would be stored in the same set of tables, using a column (like customer_name) to identify the customer. This makes schema changes easier as I would only need to update one table. However, this approach compromises data isolation like a bug in the SQL logic could result in one customer accidentally querying another customer’s data, which is a major safety concern. Are there other ways to strengthen data isolation in this shared schema design?

Is there a third approach that balances both modifiability (e.g., easy column changes) and data isolation (e.g., preventing one customer from accessing another customer’s data)?

Answer 1

This is a tool problem.

Automating schema changes will be more beneficial here. Depending on laws in whatever country your application is used, data isolation may practically be mandatory.

Instead, search for tools to automate schema migrations. This will necessarily require downstream changes in your deployment process. Those custom one-off SQL scripts should be prohibited. Everything is a migration from one version to another.

Most RDBMS allow SQL scripts to be written such that they can be run multiple times, but only applied once. For example, only create a table if it doesn't exist; add a unique constraint if the constraint name doesn't exist, etc. Checking the schema name before applying a change can help with inconsistencies between databases. Look into Evolutionary Database Design for a buzz-worthy search term.

The nice thing about boring, repetitive tasks is that they can be automated with almost any programming language. Even a half-baked scripting language can give you enough power to reduce the grunt work and mistakes from doing things manually.

Some things to jump start your search:

• Active Record Migrations
• Microsoft Entity Framework Migrations
• Flyway Data Migrations
• Laravel Data Migrations
• And many more, too numerous to list.

Each technology stack should have a similar tool that offers a command line interface to facilitate scripting and automation. The command line interface is usually the most universal way to integrate schema migration tools into existing CI/CD pipelines.