Merging multiple client-specific databases into a single one

We have multiple databases as various clients. All the databases have the same schema (or structure). Now we want to merge the databases into one. We DON'T want to merge their data. We want a new design that allows multiple clients while retaining as much of the original schema as possible.

Is the simplest way to do it as follows?

• Create a client table

• On each of the other relevant tables add a foreign key constraint referencing the client table

Note: The database schema ISN'T complex at all.

Yes, that's normally how it is done.

Note that this is a non-trivial application change, pretty much every query interacting with the database (inserts, updates, selects) is going to have to include that ClientID.

Depending on what else you're storing, some tables might not need a ClientID. To make up some examples:

• If your products table is available to all clients, that won't need a ClientID

• A child table (like OrderDetail) might not need a ClientID if its parent record (the Order table) already has a ClientID.

Please see Brent's excellent post about some additional tradeoffs/consideration of this design.

StackExchange Database Administrators Q#177307, answer score: 3