1

The application I work on utilizes nested sets1 to represent tree structures within our database.
We need to expand a particular area of access control to support multiple clients who should not be able to see other client's information (aka multi-tenancy).
We have preferred the nested set approach over the more common, "naive" approach for a number of reasons as summarized in this presentation on slide 69.

At first glance, nothing within a nested set structure appears to be a problem that would prevent its use within a multi-tenant environment. However, one of the concerns with nested sets is the number of updates required leaf nodes are added or removed. For example, all entries to the right of the inserted leaf node end up needing their left and right values updated.

For the table in question, it will always remain relatively small (~5-10k rows, tops) even with multiple tenants.

We also have clients who will want additional access restrictions beyond those provided by ACLs expressed through a nested set tree.

In order to satisfy the client access restrictions and also to address potential database update concerns, we're considering adding a ClientId field to our nested set table. The idea being that updates to one client's tree won't need to affect the trees of other clients. Likewise, we'll have a hard block in place if Client A tries to inadvertently access Client B's data because the Client field won't match.

| RowId | ClientId | Left | Right | Foo... | Bar... |
|-------|----------|------|-------|--------|--------|
| 3     | 10       | 1    | 42    | ...    | ...    |
| 5     | 20       | 1    | 69    | ...    |        |

One complication that I have is that I have a set of users (call them "ClientId 0") who will perform actions for the other clients (like "Client 10", "Client 20"). And the actions that "Client 0" perform need to be visible / usable by the client they performed the action for. So "Client 0" has to create things that will receive a different ClientId.2


Does this approach appear to be sound, or what should I be examining to understand if it will hold up as I explained above?
Alternatively, is there a superior data structure in order to represent multi-tenant, hierarchical information?

1Also see this slidedeck starting at slide 53 for additional details of implementing a nested set tree structure.
2My working approach to resolve this is to pull the ClientId from the node that Client 0 is working on, and assign the node's ClientId to the newly created node.

  • I believe you mean "we're considering adding a ClientId field". – Frank Hileman Feb 12 '15 at 17:54
  • @FrankHileman - yes, you're correct. Thanks for catching that and I've updated it. – user53019 Feb 12 '15 at 19:51
  • I don't understand why the ClientId column doesn't take care of everything. I understand you have one client that can modify things for other clients, but can you not simply use impersonation? Use the ClientId that is specific to Client 10, etc, from Client 0? It is not clear why this is not a valid option. – Frank Hileman Feb 12 '15 at 22:32
  • @FrankHileman - my main concern is using a nested set in a multi-tenant table. I think the ClientId field will protect other clients from feeling any pinch from using a nested set, but I can't find anything definitive to answer that. And I think that the left / right indexing will update just fine on a per ClientId basis, but again, I can't find anything that says one way or the other. – user53019 Feb 12 '15 at 22:43
  • 1
    Adding a "tenant" column (ClientId in this case), and using that to filter by tenant, is logically identical to having a separate database for each tenant. The only difference is, you have to remember to use that everywhere. This is true for everything in the database, not simply your nested sets. – Frank Hileman Feb 12 '15 at 22:46
2

Adding a "tenant" column (ClientId in this case), and using that to filter by tenant, is logically identical to having a separate database for each tenant. The only difference is, you have to remember to use that everywhere. This is true for everything in the database, not simply your nested sets. You are safe, assuming you are careful in writing the stored procedures.

Whatever advantages or disadvantages you found in the chosen hierarchy representation, will still be present even after making it a multi-tenant database. The only difference is, now you have a bit more work to do on each query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy