Multitenancy and nested set trees

Question

The application I work on utilizes nested sets¹ 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.²

---

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?

¹_{Also see this slidedeck starting at slide 53 for additional details of implementing a nested set tree structure.}
²_{My 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.}

Answer 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. 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.