How would you define the system architecture (mostly in terms of database structure) for a system which needs to have data available both at tenant level and organization level. An example of this is the following: A business is a client (let's say OAuth2 client) in the system. The business can have multiple subsidiaries. Each subsidiary is a tenant in the system. The business users want to have this functionality available to them:
- subsidiary users should be able to access only their own data if the data was created only for their subsidiary (for e.g. sold items, employees, invoices, etc.)
- subsidiary users should be able to access company wide data (for e.g. the list of suppliers and customers, country/city dictionaries and any other dictionaries that might be common to all of the subsidiaries). This data should be available to all users to read/write/delete.
- data should be allowed to move between subsidiaries (for e.g. when employees are re-assigned to another subsidiary)
My first attempt at this type of system was to have the tenancy described at table level through a discriminator column and to model the "system wide" information by having a null value for a tenant column. The concern is that this will impact the development of the application as the "tenant" information will need to be known, communicated and enforced throughout all the levels of the application down to the data access layer as opposed to the "schema" or "database" per tenant approach where the tenancy can be handled in a more elegant way at connection level. Is there any other approach I can take to allow this functionality without making the code tenant-aware in so many places?