3

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?

  1. Create a client table
  2. 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.

1

2 Answers 2

3

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.

4
  • I do it by having a global user table and then user-specific tables. For example, user1234_items would hold the items for user 1234.
    – Kae Verens
    Commented Jun 26, 2017 at 22:18
  • That eliminates the chance of one user seeing another's data
    – Kae Verens
    Commented Jun 26, 2017 at 22:19
  • 1
    @KaeVerens sounds like you should post that as an answer, not a comment. Although duplicating every table in the same database sounds like an even worse solution than duplicating the database as a whole.
    – Kat
    Commented Jun 26, 2017 at 22:24
  • Sorry - first-time using the phone app!
    – Kae Verens
    Commented Jun 26, 2017 at 22:25
0

I do it in a database with a few hundred clients and their subusers.

All users have an ID and a ParentID.

Each parent user has their own set of tables, with a name such as user1234_stock or user1234_things.

Main advantages:

  • no chance of a query "leaking" another user's data.
  • less overhead than completely separating by database because there is just one database.
  • backups are easy. Just backup a table at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.