Lookups in Multi-Tenant Database

Question

I am developing a SaaS application and I am looking for the best way to design lookup tables, taking in consideration:

• The look-up tables will have predefined data shared among all the tenants
• Each tenant must have the ability to extend the look-up table with his own data e.g adding a car class not defined

I am thinking about adding TenantID column to each lookup and add the predefined data with setting that column to some value which represents the "Super Tenant" that belongs to the system itself

Answer 1

In a shared schema setup I would strongly recommend you identify the "owning" tenant of every table. Granted it's not always necessary, as child tables can be owned by parents which are owned by the tenant, but having the tenant on every table makes life much simpler and safer. Having said that, I would use a special value for the tenant and either union or OR the select to get combined results. You could use null, -1 or a well-known guid to represent the system. My preference has been to use -1 recently.