0

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?

Improve this question
3
  • "Each subsidiary is a tenant in the system." Why? What does that mean to you? Trying to understand.
    – John Wu
    Commented Nov 3, 2020 at 6:56
  • Hello John. Thank you for the interest. The idea is that users are employees of a subsidiary. Each subsidiary has own data (warehouse stock, items being sold, invoices (even different legal/accounting entities), prices for items and other similar information) and the users of one subsidiary should not manage the data of the others. An employee from Berlin should not be able to change the stock of the Hamburg subsidiary. On the other hand, both subsidiaries work with the same type of items, the same set of dictionaries (countries, currencies, cities, etc.) and customers. Hope this helps.
    – Andrei Zafiu
    Commented Nov 3, 2020 at 8:37
  • If you want to achieve this without making the code tenant-aware you will most likely have to handle it at the database level, and that means that the solution is dependent on the actual database system that you use.
    – Hans-Martin Mosner
    Commented Nov 13, 2020 at 14:50

1 Answer 1

Reset to default
2
+25

In our application we needed something similar, what we did was create an "organization units" table (that was the name in our case) and then each record may be linked to 0 or more OUs, if it is linked to at least one then it is visible only to users enabled to that OU, else it is visible to every user.

This method implies that whenever you query for a record type also left join it with the OUs link table, putting a condition where the OU ID must be either null or equal to one of the OUs visible for the current user.

Improve this answer
4
  • Hello Matteo. Can you tell me how is this different from the solution I suggested? I mean what you proposed here is the same solution to a slightly different problem (in your case a user could be part of multiple tenants and should be able to see any of the tenants information) but in principle, the null tenant id or a specific tenant id is the same solution as the "organization unit". How do you pass in the tenant information throughout the multiple levels of programming (rest api, service layer, database level)?
    – Andrei Zafiu
    Commented Nov 8, 2020 at 19:31
  • In our case, we store the OUs IDs in the user's session data, which is then used as a parameter to the various data accessing methods, as needed.
    – Matteo Tassinari
    Commented Nov 8, 2020 at 20:58
  • I work on programs with a similar issue. We map the login id to a set of "organisations", these are linked in where sensible to our domain objects. Users can only see/edit those OUs they have the rights for.
    – Kain0_0
    Commented Nov 8, 2020 at 22:46
  • Hello. The question was more in the sense of how can you design the actual application to be single-tenant apart for a few components of the application which know how to handle the tenant specific information. If it were a schema level multi-tenancy, only the database connection needed the tenant information extracted from session/token/anywhere in order to properly select the schema and the main application can work as if in single tenant mode. The same is true for database multi-tenancy. The question was more of how can we replicate this with the extra requirements(null schema is a no-go :)
    – Andrei Zafiu
    Commented Nov 9, 2020 at 18:43

Your Answer

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

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