Row Ownership

I'm currently researching best practices for developing a multi-tenant SaaS application and I'm planning to use LightSpeed for my implementation. A standard practice that I have encountered is to give every row in the database a tenant id column. I'm currently evaluating whether this is necessary when using LightSpeed and if so, how to implement it. In my scenario, my tenant id is the PK from the Companies table. All entities with a direct relationship to companies has a FK that links back to Companies. Secondary relationships do not, and I don't know exactly how to implement these relationships.

For example, a company can have multiple locations, and each location can have an address. The locations table has a FK relationship back to the company, but the addresses table does not. Is there anyway to add a company ID FK to each entity such as an address that is automatically set whenever I add that address to a location that is owned by a company?

Its very possible I'm looking at this the wrong way, so I'm open to LightSpeed centric suggestions / practices as well as long as they allow me to securely partition my data.

Thanks,

Matt

There's no way in LightSpeed to automatically add a foreign key to 'secondary' relationships such as address. In general of course it's a bit doubtful having that information in the address table anyway, because it effectively denormalises the data which could lead to integrity issues (suppose Address A is associated with Location B: what happens if Location B's CompanyID points to Company C, but Address A's CompanyID points to Company D?), but I can see that it makes sense for a tenanting situation.

One way you could populate the tenant ID is to do it at save time by overriding the OnSaving method. OnSaving would locate the entity from which you want to 'inherit' your tenant ID and set it on the local object:

partial class Address {
  protected override void OnSaving(...) {
    if (EntityState == EntityState.New) {
      this.TenantId = this.Location.TenantId;
    }
  }
}

This assumes the parent TenantId is always set up before first save and never changes, which seems a reasonable assumption. However if, say, a Location could change from one Company to another, then this approach would not work. (But in a multitenant scenario I assume that will never happen because tenants theoretically don't know about each other.)

Another possible solution might be to use computed columns or views in the database so that the data remains normalised but is denormalised when selected. However views would probably cause save issues and I am not sure of the feasibility of using computed columns if there are potentially long traversal chains.

Finally, to be clear, none of this actually secures the data from the LightSpeed point of view. LightSpeed will cheerfully select data from multiple tenants if a developer forgets to filter on tenant ID. In recent nightly builds we have added a feature that allows you to inject additional filtering at the entity level rather than having to remember to write it on each query: see http://www.mindscapehq.com/forums/thread/261704 for details (and we'd love to hear feedback if you decide to use this feature!).