I am creating a multi-tenant (shared-schema) database for a SaaS application. The application will allow the subscribing company (the tenant) to collaborate with other companies (accounts – such as vendors, business partners, customers, etc.). Users will be associated with both the tenant and the accounts.
Here is my question: from a design perspective, is it okay to put the tenants and accounts in one table? I’m thinking “yes” because a company is a company regardless of whether they are a tenant or an account. Further, I was thinking of deciphering a tenant with a field such as is_tenant (Boolean) and perhaps put tenant specific information in a separate table. Here is a proposed schema:
- companies (company_id, is_tenant, name, address, etc.)
- users (user_id, name, email, username, password, etc.)
- company_users (company_id, user_id)
- tenant_information (company_id, billing_address, billing_state, etc.)
- tenant_accounts (tenant_id, account_id) – associates tenants with accounts [where tenant_id and account_id would be f_keys to the companies table]
I read through the MS article, Multi-Tenant Data Architecture and, while helpful, it was short on providing an answer.
Any additional thoughts/comments/pitfalls regarding this schema would be greatly appreciated.
Thanks in advance.
