Database Client Specific Tables v/s Relational Tables

Question

I have a scenario, my application is a SAAS based app catering to multiple clients. Data Integrity to clients is very essential.

Is it better to keep my Tables

1. Client specific OR
2. Relational Tables

For Ex: I have a mapping table with fields MapField1,MapField2. I need this kind of data for each client.

Should I have tables like MappingData_

or a Single Table with mapping to the ClientId

MappingData with Fields MapField1,MapField2,ClientId

Answer 1

I would have a separate database for each customer. (Multiple databases in a single SQL Server instance.)

This would allow you to design it once, with a single schema.

• No dynamically named tables compromising test & development
• Upgrades and maintenance can be designed and tested in one DB, then rolled out to all
• A single customer's data can be backed-up, restored or dropped exceedingly simply
• Bugs discovered/exploited in one DB won't comprise the integrity of other DBs
• Data access (read and write) can be managed using SQL Logins (No re-inventing the wheel)

If there is a need for globally shared data, that would go in another database, with it's own set of permissions for the different SQL Logins.

The use of a single database, with all users in it is my next best choice. You still have a single schema. But you don't get to partition the customers' data, you need to manage access rights and permissions yourself, and a whole host of other additional design and testing work.

I would never go near dynamically creating new tables for additional customers. A new table name means all your queries need to be updated with the new table name, and a whole host of other maintenance head-aches.

I'm pretty much of the opinion that if you want to create tables dynamically during the Business As Usual use of an application/service, you've designed it badly.

Answer 2

SO has a tag for the thing you're describing: "multi-tenant".

Visualize the architecture for supporting a multi-tenant database application as a spectrum. At one extreme of the spectrum is "shared nothing", which means each tenant has its own database. At the other extreme of the spectrum is "shared everything", which means tenants share tables, and each row in each table belongs to one tenant. (Each row contains a tenant identifier.)

Terminology seems to overlap, so read carefully. What one writer means by shared schema might be identical to what another writer means by shared everything.

This SO answer, also written by me, describes the differences and the tradeoffs in terms of cost, data isolation and protection, maintenance, and disaster recovery. It also links to a fairly good introductory article.