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 modifiable.

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)?

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)?