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.