1

My boss is currently working on a database design for a multi-tenant capable ERP/CRM system, which will have a Sql Server backend.

Some key points of the design:

  • A server instance will host a "shared" database (unique model), plus one database per company (same model)
  • The "shared" database have tables like contacts, users, etc. that will be referred to by other company databases.
  • The "shared" database have triggers that will check if its data can safely be deleted (if it's used by any company, the DELETE operation must fail)
  • If, for security concerns, a company must NOT use shareable information, it must be set up on its own server instance, with its own "shared" database that will, in fact, be used only by that company.

And here's an implementation example for a system:

SERVER INSTANCE 1:

  • Shared DB
  • ACME
  • WayneEnterprises
  • KwikEMart

SERVER INSTANCE 2:

  • Shared DB
  • Umbrella

My boss stands by this design on the following believed benefits:

  • Since there's one db per company, smaller companies like KwikEMart won't suffer from the weight of the bigger ones.
  • User access can be defined on a "per-company" strategy. This way, Apu can be granted the privilege to read KwikEMart's Invoices table, but not ACME's.

Although I understand these benefits, the integrity tradeoffs seems overwhelming to me.

First, there's the obvious integrity cut-off between companies db and the shared db. It's impossible to make foreign keys across db boundaries.

Second, what if the Joker messes up with WayneEnterprises database so bad that Batman will have to restore a day-old backup? If ACME, on that very same day, decided to remove a shared Gotham address that was still in use in the older WayneEnterprises db, then every document which used that address won't be able to display it anymore. Unless of course there's a shared db backup from the exact same time that can be restored. But EVEN if there was one, it could lead to even more problems for both ACME and KwikEMart.

And that's just the two issues that popped right into my mind, there's probably others as well.

So my question here would be: are we getting in the right direction with this? And also, how does this kind of system is normally built?

Any guidance will be much appreciated.

Improve this question
17
  • 4
    What is the purpose of the shared DB? Why not just have one self-contained database for each company?
    – Robert Harvey
    Sep 23, 2015 at 15:40
  • 3
    Also, it's called "Multi-Tenant," if you want to research it.
    – Robert Harvey
    Sep 23, 2015 at 15:43
  • Are the databases accessed directly by your customers? To be honest I think he is making a tall assumption that a database with a complex schema is "always" slower than a smaller complexity database. That is like saying that All C++ programs are faster and more efficient than all Java programs, which is also an unfair stereotype with many many variables that factor into it. Honestly it sounds like you want to have a rich domain model that satisfies all customers and encapsulate your direct database access through web services.
    – maple_shaft ♦
    Sep 23, 2015 at 16:15
  • @RobertHarvey The shared DB's purpose is to hold on data that would be used by multiple companies, like customer addresses, phone numbers, a list of countries, order statuses multilingual descriptions, etc, etc... for example, both ACME and Wayne Enterprises could be doing business with a customer named Stark Industries. If Stark were to change its business phone number or move out to another city, an attendant would only have one place to go to update that data, rather than going through every company that potentially does business with Stark.
    – Crono
    Sep 23, 2015 at 16:39
  • You can make foreign keys across the database boundaries if you use GUIDs instead of INTs for the keys.
    – Robert Harvey
    Sep 23, 2015 at 16:49

1 Answer 1

Reset to default
7

In the multi tennent systems I have worked on as a DBA and Developer we used 1 database per client. That database was completely self contained and did not rely on any shared database, not even for things like the states in the US.

To make standing up a new client easy a model database was created that had all the things like States prepopulated.

The reason we did not store addresses or other common data in a shared database is that it allowed us to move the databases around several different servers to normalize the load accross the servers we had. If a database needed a higher SLA due to a client upgrade we could just take a backup and restore the client database on the appropriate server and not need to worry if "the address for Stark Industries" existed on the new server.

The obvious question is what happens when client A updates the phone number or address for a customer? Should client B see that change? Wouldn't that lead to "duplicated" data if every thing is stored in seperate databases?

What if "Stark Industries" wants your Client A using a different phone number than your Client B? Then you will have to have a way to show that and store the different numbers in the Client database or move Client A and Client B to different instances. I am sure as you look through your shared database you will find additional instances of this problem of different clients wanting to use data differently.

For updates to the common tables a script that loops through each database is rather easy to write, a cursor on sys.databases and some dynamic SQL takes care of that part.

Overall while having a shared database may look good from a normalization standpoint once you start to look at different ways that clients use the different data, a shared database may not make sense any more. If you are going to go the route of seperate databases per client, just go full bore and go completely seperate with no shared data.

Improve this answer
6
  • +1, shared-nothing designs are much simpler, and the reasons against them always seem to be premature optimization (or ethically-dubious aggregation of data).
    – kdgregory
    Sep 24, 2015 at 11:19
  • Your "different phone" number is indeed a scenario that came up early in the analysis phase. My boss's solution for that was to allow multiple phone numbers for a customer, and a link table inside the tenant's databases. The shared db's role is basically to hold on to actual and undeniable facts, like "there is a state in the US called New Jersey", "that phone number exists no matter who owns it", "there are people doing business at that address", etc, etc. I do believe that's overkill but unfortunately I don't have enough experience to talk my boss out of it, which is why I asked here.
    – Crono
    Sep 24, 2015 at 12:11
  • @Crono While a common database would seem to make sense I have always found (as kdgregory stated) its premature optimazation. The dificulties introduced in developing out weigh the benefits. While yes there is a state in the US called New Jersey the extra space of a less than 1mb (mB?) table over a hundred databases is increadibly cheap compared to the extra time for development and testing. a 1TB western digital Red (enterprise grade) runs about $65. If it takes more than 1 hour of extra dev time it just cost the company money.
    – RubberChickenLeader
    Sep 24, 2015 at 14:03
  • @WindRaven Of course space is not the concern here, it's much more about data synchronization. Out of sync data can cost a company money too. Granted, having "New Jersey" for tenant 1 and "New Ejrsey" for tenant 2 probably isn't that bad. What could be bad though, is sending a 20 000$ worth shipment of perishable material in the wrong state because a customer's address wasn't updated for every company db (although at this point there's probably just too much incompetent people in the processing chain :) ). In any case, that's the kind of thing my boss aims to eradicate.
    – Crono
    Sep 24, 2015 at 15:20
  • 1
    @Crono It comes down to how much hand holding you want to do. If you make your Clients responsible for having the correct address it is on your clients. To turn your situation around, Client A changes the address for "Stark Industries" which causes Client B to send a 20K shipment to the wrong state for Stark Industries. Who is responsible? Client A for changing the address? Client B for not noticing? or Your company who designed the system that way? .....
    – RubberChickenLeader
    Sep 24, 2015 at 15:44

Your Answer

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

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