Hi everyone,

I am designing a multi-tenant database for a SaaS application. I currently have tables that store tenant information (the subscribing company that pays for the service), users, and an associative table that associates the users with the tenant company. The application will allow tenants to collaborate with other companies (i.e. accounts - such as vendors, partners, etc.) and allow users from those companies to login via UN/PW. Here are my questions:

Should I have separate tenants and accounts tables? These tables would store, more or less, the same information. The tables might look like this:

• tenants (tenant_id, name, address, etc.)
• users (user_id, first_name, last_name, etc.)
• tenant_users (tenant_id, user_id)
• accounts (account_id, name, address, etc.)
• account_users (account_id, user_id)

Or, should I have one table called companies with an “is_tenant” field, for example, that differentiates subscribers? The advantage to this would be that if an account wanted to become a tenant (subscriber), then it would just require changing the “is_tenant” field. The tables might look like this:

• companies (company_id, is_tenant, name, address, etc.)
• users (user_id, first_name, last_name, etc.)
• company_users (company_id, user_id)
• company_accounts (tenant_id, account_id) - where tenant_id and account_id are f_keys of the companies table

It seems to me the second design is better but I’d enjoy hearing from your experience. If you have additional thoughts/insights I'd enjoy hearing them as well.

Thank you in advance - Piet