Partition ID column
New tenant creation – A new row is added to table with the new value in partition/tenant Id column. Other information for this tenant is correlated(with other tables) using the partition Id.
Usage – This is useful in cases where the data being inserted into the tables from multiple tenants is not going to result in bulky tables.
Tenant specific tables
New tenant creation – A new set of tables get created whenever a new tenant is onboarded. Usually the table names have tenant/partition Id as part of their names.
Usage – This is useful in cases where the data from multiple tenants is going to result in a bulky table. With this approach, we are sharding the data across tenants to begin with. This helps to handle any performance constraints.
Hybrid
New tenant creation – Refer above two approaches.
Usage – This is where we use the Partition ID column approach in most of the places. Only in places where the data is gonna grow and it getting queried from multiple tenants may have performance impact, we can create a tenant based table in those cases.
Inheritance
As happens in inheritance, the child object inherits the properties of the parent, this approach tries to simulate that behavior. A base table is maintained which would hold data which is common to all the tenants, both existing or those which are added in future. Tenant specific information is stored in separate tables where tenant ID value can be one of the columns. At the time of fetching the data, data from the base table and child table is captured(may be using a union) and returned.
New tenant creation – No new data is usually added in such schema whenever a new tenant is onboarded.
Usage – This can be used where there is a common data set shared by all the tenants.