Today’s post is written by Mark Burns, Senior Access Developer and our latest hire at IT Impact, Inc. Mark is starting a series of posts on how to create, configure and use Access with SQL Server with multitenancy. To learn more about Mark please visit his profile page here.

Multi-tenant Database Design

Occasionally, we come across projects where there is a need to store information from multiple entities into one database. One common answer to the problem is to use multitenant design. Consider it as having multiple identical databases in one physical database.

One Business, Multiple Locations

In one case, we saw a company with multiple different, but independent business locations. While the management teams at each location were different, the data requirements and management reports were all identical. In this type of business environment, the requirements to isolate the individual business locations from one another are few, and the benefits of consolidated reporting and data analysis are far greater. So, a multitenant design can look extremely attractive for these situations.

In the second case, suppose there’s a restaurant Management Group that runs different franchises. The various franchises are more or less independent from one other but all report to single parent corporation. However, they may also report to different centralized franchising corporations. It may make sense to house at least some their data in one database since they may share common business management infrastructure as parts of the larger group. For example, they may utilize identical financial reporting formats for their management group, but also need different reporting for their respective franchise systems.

Additionally, they may share common purchasing, budgeting, accounting, and HR information systems, but each may need distinctive inventory, equipment, and business rules. The Management Group can distribute a single application – with customized, data-driven options for each franchise or location – to all franchises and each franchise can only view their own data while corporation can examine data across multiple franchises more easily with a shared infrastructure.

Multiple Businesses with No Relation

The next case is when several smaller businesses may find it more economical to pool their resources into an shared application via internet. SalesForce and QuickBooks Online are two notable examples where business can enjoy the benefit of a full-blown, supported and mature line of business applications without the expenses that would come with it if they developed it entirely in house. In this example, nobody, including the owner of application has a need to work with data across tenants but the convenience of a single database store simplifies the administration behind the application – the owner only need to scale out more servers as opposed to installing private instance of their applications on each client’s premise and dealing with plethora of configuration and deployment issues.

On the other hand, it may be very undesirable to take any risk of having one company’s data become accessible to another one (possibly a competitor). Also, if the tenants each have sufficiently dissimilar needs and data requirements, multitenant solutions are ill-advised.

The Basics

  • The first requirement is the ability to logically separate one tenant’s data from another’s.

If you view each tenant as is if they had an office on one floor in a multistory office building, each would have access to their floor and all the data in their office space. The applicable Business Rules will determine the appropriate security arrangements and whether tenants on one floor have access to the other floors and their data, and to what extent.

  • The second requirement is, conversely, to be able to logically combine them when and where appropriate.

Given our office building analogy above, the Fire Marshall is concerned with the escape routes and entry points into the building and how many occupants there are, and perhaps what materials and storage areas there are. So, the Fire Marshall needs certain information from all the tenants – and they may need access to all areas of the building for safety inspections as required. While the Fire Marshall might not care how many file folders there are in each office on each floor, they may well care whether there are 100,000 file folders in all filing cabinets building-wide, or 2,500,000 folders in all the file cabinets in the whole building. So, this consolidated view of the information from all tenants needs to be available.

The requirements above leave us free to include designs where the data for multiple tenants is intermingled physically in common data structures to one degree or another, but it also leaves us free to physically segregate the data into distinct physical database structures, so long as we have a means of making them appear combined when appropriate.  Often our technology choices help us make some of these design choices for us, but generally there is still enough flexibility to allow us to be quite creative in deciding how to implement a multitenency system under the covers.

Physical Separation: The most secure approach

When we pursue this type of design strategy, it’s not multitenant, rather we are emphasizing the segregation of the data of one tenant from another. We literally are creating physically separate database structures for each tenant, whether at the server, database, or data table levels.

While it assures segregation of the data for each tenant, it complicates the maintenance of all the separate copies of the data containers and their descriptive metadata.

That also means complications can arise in the system between different versions of the data structures as well, if they are not all carefully kept in proper synchronization. Depending upon how many tenants and how many data structures are involved, over time, this can become its own kind of ongoing maintenance nightmare.

On the other hand, this provides possibility of greater flexibility in planning and rolling out version changes and upgrades to different tenants on different schedules (something often demanded by real-world business conditions).

Also, in this type of design with physically segregated set of data stores, making all the data look logically combined becomes increasingly difficult – depending upon the implementation details – along with the number of tenants and the number of copies of the data structures and their possible version-related variations.

———-

In the next article in this series, we’ll provide tips on database design for multitenancy in Access with SQL Server.