Multi Tenant Database with some Shared Data

Question

I have a full multi-tenant database with TenantID's on all the tenanted databases. This all works well, except now we have a requirement to allow the tenanted databases to "link to" shared data. So, for example, the users can create their own "Bank" records and link accounts to them, but they could ALSO link accounts to "global" Bank records that are shared across all tenants.

I need an elegant solution which keeps referential integrity

The ways I have come up with so far:

1. Copy: all shared data is copied to each tenant, perhaps with a "System" flag. Changes to shared data involve huge updates across all tenants. Probably the simplest solution, but I don't like the data duplication
2. Special ID's: all links to shared data use special ID's (e.g. negative ID numbers). These indicate that the TenantID is not to be used in the relation. You can't use an FK to enforce this properly, and certainly cannot reuse ID's within tenants if you have ANY FK. Only triggers could be used for integrity.
3. Separate ID's: all tables which can link to shared data have TWO FK's; one uses the TenantID and links to local data, the other does not use TenantID and links to shared data. A constraint indicates that one or the other is to be used, not both. This is probably the most "pure" approach, but it just seems...ugly, but maybe not as ugly as the others.

So, my question is in two parts:

• Are there any options I haven't considered?
• Has anyone had experience with these options and has any feedback on advantages/disadvantages?

Answer 1

A colleague gave me an insight that worked well. Instead of thinking about the tenant access as per-tenant think about it as group access. A tenant can belong to multiple groups, including it's own specified group. Data then belongs to a group, possibly the Tenant's specific group, or maybe a more general one.

So, "My Bank" would belong to the Tenant's group, "Local Bank" would belong to a regional grouping which the tenant has access to, and "Global Bank" would belong to the "Everyone" group.

This keeps integrity, FK's and also adds in the possibility of having hierarchies of tenants, not something I need at all in my scenario, but a nice little possibility.