I have a complete database with several tenants with TenantID on all rented databases. All this works well, except that we now have a requirement to allow linked databases to "communicate with" shared data. For example, users can create their own “Bank” records and link to them, but they can also link accounts to “global” bank records that are shared among all tenants.
I need an elegant solution that maintains referential integrity
The methods that I have come up with so far are:
- Copy : all shared data is copied to each tenant, possibly with the System flag. Changes in general data include huge updates for all tenants. Perhaps the easiest solution, but I don't like data duplication.
- Special identifiers : all references to general data use a special identifier (for example, negative identification numbers). They indicate that TenantID should not be used in relation. You cannot use the FK to properly comply with this rule and, of course, you cannot reuse the identifier from tenants if you have ANY FK. To ensure integrity, only triggers can be used.
- Separate identifiers : all tables that can refer to common data have TWO FK; one uses TenantID and refers to local data, the other does not use TenantID and links to shared data. A restriction indicates that one or the other should be used, not both. This is perhaps the most “clean” approach, but it just seems ... ugly, but maybe not as ugly as the others.
So my question has two parts:
- Are there any options that I have not considered?
- Has anyone had experience with these options and has any feedback on the advantages / disadvantages?
source
share