Archive for the ‘ MySQL ’ Category

Multi-Tenant Database Design

@Work, we’re a Software as a Service (SaaS) (although we’re evolving into a platform) company providing data integration (perl ETL) and BI reporting and other industry (Business Equipment) based solutions such as territory mapping and compensation programs. Like most SaaS companies, database design, architecture and security are of the utmost importance, but how do you go about selecting the ‘right’ multi-tenant solution for your problem? As you start this requirement gathering and analysis process, you undoubtedly will start coming up with more questions, like :

  • Are there laws and regulations in place that your application / data storage solutions must conform to (Safe Harbor laws, encryption standards, credit cards, etc)?
  • How do you ensure only authenticated users have access to the data their authorized to view / manage?
  • How do we handle fail-over scenarios and reduce our risk for potential transactional data-loss.
  • How do you deal with backups, while keeping the aforementioned items in check?
  • What’s your restoration process, has it been tested (frequency of tests)?
  • How is the performance of your application going to be affected by any design decisions you make?
  • And so on and so on…

When dealing with Multi-Tenant data, I like to design one master schema (db in mysql), and then create one schema for each customer / client / vendor / tenant, etc. This design allows for a quick and easy encapsulation of the customers data, fast(er) backup and restorations processes and it can offer some natural security benefits if you tie the database user into the schema and of course YMMV.

Downtime and / or data loss in 2012 should be unacceptable (sure, there’s maintenance and migrations to consider, etc) to any SaaS providers and the costs associated with them should be VERY expensive. There are various ways to handle the fail-over configurations of MASTER / SLAVE relationships for this design and MySQL’s replication and / or a continuent solution may be the right one for you regarding this critical topic and again, YMMV. I’ve used a combination of solutions in the past, but the basic premise is to keep it as simple as possible while maintaining near 100% uptime and redundancy across the MASTER / SLAVE clusters.

There are many great articles available on the web that go into this concept in much greater detail than I will, with this one being one of the top resources available.