2

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.

Improve this question
3
  • Multi-tenant architecture can mean anything from "shared nothing" (one database per tenant), to "shared everything" (every table contains rows that belong to many different tenants). It sounds like you've already decided on a multi-tenant architecture. Have you?
    – Mike Sherrill 'Cat Recall'
    Nov 10, 2016 at 14:48
  • Would vendor can company ever reference the same CompanyMaster.ID?
    – paparazzo
    Nov 15, 2016 at 18:08
  • Not a lot of people have experience working on a system at the scale you described, much less designing such a system. I strongly recommend you talk to your local Microsoft rep to work with them on this. For a project at the scale you described, you'll benefit from working with the SQL Customer Advisory Team (SQLCAT) and your local Microsoft contact can help you get connected. SE or any other public forum is a tough place to have the kind of deep discussions and design reviews that you'll likely need. The issues around security and noisy neighbor are not trivial to solve.
    – SQLmojoe
    Nov 17, 2016 at 18:29

1 Answer 1

Reset to default
1

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.