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
SERVER INSTANCE 2:
- Shared DB
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
Invoicestable, 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.