How to design a multi tenant mysql database

mysql database database-design multi-tenant
gaurav picture gaurav · Apr 6, 2011 · Viewed 28.6k times · Source

Let us say I need to design a database which will host data for multiple companies. Now for security and admin purposes I need to make sure that the data for different companies is properly isolated but I also do not want to start 10 mysql processes for hosting the data for 10 companies on 10 different servers. What are the best ways to do this with the mysql database.

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Apr 6, 2011

There are several approaches to multi-tenant databases. For discussion, they're usually broken into three categories.

  • One database per tenant.
  • Shared database, one schema per tenant.
  • Shared database, shared schema. A tenant identifier (tenant key) associates every row with the right tenant.

MSDN has a good article on the pros and cons of each design, and examples of implementations.

Microsoft has apparently taken down the pages I referred to, but they are on on archive.org. Links have been changed to point there.

For reference, this is the original link for the second article

Related questions

Auto Generate Database Diagram MySQL

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a …

Read More
What does character set and collation mean exactly?

I can read the MySQL documentation and it's pretty clear. But, how does one decide which character set to use? On what data does collation have an effect? I'm asking for an explanation of the two and how to choose …

Read More
MySQL - how to front pad zip code with "0"?

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up. The clean zip code data is when I have all 5 digits for a zip code (e.g. "90210"). But for some reason, I noticed …

Read More