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.