I'm creating a SaaS application and i'm searching for the easiest method to design research tables, consuming consideration:

  • The appearance-up tables may have predefined data shared of all the tenants
  • Each tenant should have the capability to extend the appearance-up table together with his own data e.g adding a vehicle class not defined

I'm considering adding TenantID column to every research and add the predefined data with setting that column with a value which signifies the "Super Tenant" that goes somewhere itself

Inside a shared schema setup I'd highly recommend you identify the "possessing" tenant of each and every table. Granted it isn't always necessary, as child tables could be possessed by parents that are possessed through the tenant, but getting the tenant on every table makes existence easier and safer. With that said, I'd make use of a special value for that tenant and only union or Or even the choose to obtain combined results. You could utilize null, -1 or perhaps a well-known guid to represent the machine. My preference is to use -1 lately.