In this post, I’m going to briefly cover the signs that you’re doing multi-tenancy wrong. Some of these practices are entrenched in software: there are gems in Ruby on Rails, for instance, use the first anti-pattern to achieve multi-tenancy. Listen, you can drive a car with a flat tire and you can eat yogurt with a fork. People have made these solutions work, but there’s a better way.
Creating tables or schemas per customer
If you find yourself running DDL (Create Table…) for each new company or user that you add to your system, most likely you’re committing a pretty big anti-pattern. Now every time you update the table definition or need to update data across all tables, you’ll have to use a script to generate the SQL for you. Those updates will take longer and it’s much more prone to failure.
If you’re doing this for performance reasons, you have two options in most database systems to physically separate customers while maintaining the same logical tables: indexes and partitioning. Indexes, obviously, are the lighter option. As mentioned in my first post on multi-tenancy, you’ll often search the tables by the tenant id (whatever that is), so it’s often wise to include it an index or just to index it by itself. You can extend this idea by using the tenant id as partition key, so the database system would create multiple physical tables beneath the surface while you enjoy the benefits of having one logical table.
Multiple accounts for the same user
Another common mistake is to tie a login or user to one tenant. In many circumstances, you’ll want to give a user access to multiple tenants. In the previous post on many-to-many mapping, I talked about creating mapping tables that model these relationships. If you find yourself creating multiple accounts for the same person, this is a sign you need to use a many-to-many mapping.
Even if there’s a possibility of a user accessing multiple tenants, include it in your initial design. You could keep your database table as a one-user-one-tenant, and then provide an API function that you can use to check whether a user has access to a company’s data:
Maybe you never go to many-to-many, but if you do, this will allow you to change your code in a two places (this function and the function that retrieves your User data from the database). Most likely, you will have this need from the get go. Customer support personnel and relationship managers often need access to multiple accounts from the very beginning.
Duplicating data
Here’s a killer anti-pattern: syncing data back and forth between two tenants or users. I’ve seen batch jobs that check to make sure that if a change is made to tenant X’s data, it is then applied to tenant Y. This is no way to live, and it indicates that you need many-to-many mapping. I’ve also seen this done manually, where an account manager has to enter or update the records for tenant X, Y, and Z at the same time. When you have to create the same data many times, the only difference being the tenant id, you have a problem and you need to think in terms of multi-tenancy.
This is an instance of the granddaddy of modeling mistakes – that is, denormalization. Like other denormalization, it is fraught with issues. If your job or manual process doesn’t work perfectly, your data could be correct in one place but not the other. You’ll also be storing the data multiple times, creating more load for your system as well as burdening your file system unnecessarily.
The pain of multi-tenancy
When you have multiple users in your application, you will have to design your system to be multi-tenanted. You can do it well, or badly. Putting a spot of thought into your design can save you pain later.
I cover many more anti-patterns, along with the right way to do things in my book MySQL Simplified. Sign-up to hear when it’s going to come out!
Want to hear more about database design, specifically with MySQL? Well, I’m writing a book about it! You can subscribe below and I’ll send you updates. I’ll also send out stuff on MySQL like tips and tutorials.
Email
Your Name
No spam
PlanetMySQL Voting:
Vote UP /
Vote DOWN