I am developing a database for pure multi-level work (one database, one schema), and I would like to keep Tenant_Id in most of my tables as a security measure to ensure that the data does not fall into the wrong tenant's hands. This seems to require a composite key for each table.
Example:
In a single tenant, I will have one primary key:
Animal_Id (PK)
Animal_Type
Animal_Name
In Multi-tenant conditions, I will add another primary key for Tenant_Id:
Animal_Id (PK)
Tenant_Id (PK)
Animal_Type
Animal_Name
Adding a Tenant_Id column to each table means I need to have a composite key in each table, or is there a safe way to avoid this? The composite keys are fine, but I would like to avoid them if I can.
source