Sql-server – Multi-tenant database

azure-sql-databasedatabase-designmulti-tenantsql serversql-server-2016

We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're expecting a very large amount of data. We're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services.

My question is regarding the table structure for companies and people. A company can be a vendor, customer, contractor, prospect or financial institution. A person can be a vendor, customer, contractor, employee or prospect.

In previous databases, I have have used a master table containing the common fields and secondary tables to hold the detail pertaining to the type of company or person.

For example:

CompanyMaster (ID, Name, Website, ParentID, etc)
CompanyVendor (CompanyID, CreditLimit, CreditTerms, etc)
CompanyContractor (CompanyID, RatePerHour, RatePerMile, etc)

Similar situation for Person's table.

With regards to performance and best practice, is it better to do it this way or split them into separate tables and not use a master table?

CompanyVendor     (ID, Name, Website, ParentID, CreditLimit, CreditTerms, etc)
CompanyContractor (ID, Name, Website, ParentID, RatePerHour, RatePerMile, etc)

I have a CTO telling me that they handled 3 million transactions per day using the multi-tenant database at his previous employer. He says it's all about the indexes and I know it's more than that.

We will be hiring an Azure Partner and most likely hire/contract a DBA. I have had experience with databases that had 100K companies and 300K people using the model above, but I thought due to potential size of the multi-tenant solution that it may be better to split them into smaller tables.

Best Answer

100,000 tenants, so we're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services

You do realize that your number comes up to 2 billion entries?

I'm afraid the answer is that there are several ways to accomplish what you want to achieve. You can have a master table with your company information and then sub tables containing the users under each company. Keep in mind on incoming queries, the more joins you have to make, the longer the response time.

This is a massive design effort that will not be easily answered in this format and should be reviewed by a design team in several disciplines.

I would recommend splitting them into different logical groups either by tenant, company etc.... The main reason being any DUI ( Delete, Update, Insert) will lock the records as it runs which can potentially cause problems

Related Question