This article discusses some very subtle differences between how RI is implemented in Access and how it is implemented in SQL Server, and what this means when developing multi-tenanted database solutions. T his is not easy stuff, but if you read all the details it will explain some very important issues if you intend to build multi-tenanted databases..

At the heart of this discussion is the fact as described in our article :- Access http://www.upsizing.co.uk/Art10_MultiPartkeys.aspx uses the MATCH FULL, whereas SQL Server uses MATCH SIMPLE in the definition of how to implement RI in a database, this is very significant for multi-tenanted solutions.

Building a multi-tenanted database is very simple and involves making a tenant identifier part of the composite primary key in each table. For Example:-

So every table now has the Co field as part of the primary key. But now consider how do you create a foreign key?

It turns out that you have two options; the first option is to simply incorporate the common Co field which is part of the primary key in each table in making the relationship with the composite foreign key. In this example tblCustomer {Co,CustID} links to tblOrder {Co,CustID}.

Now this would mean that all the tenant companies were completely isolated and could not share any data; which for some applications is an acceptable policy. But in Access it has another very subtle implication. Because the Co field has to have a value you must have a value in CustId. So this means that all foreign keys become required fields! Now in the example above to have an order without a customer is meaningless; so just park that idea for moment, because we will see examples where this causes a serious problem.

The second option is to allow the foreign key CustId to be linked to data in another company (so it has its own copy of the Co field called CoCodeCustID). For example maybe we want to share the customer list over multiple companies; which we can do by constructing views to manage the available data. So we need to make the foreign key carry a different Co value to the main record, so in the related table we use CoCodeCustID to tag a value for the Co field on the relationship.

Now up to this point Access and SQL Server will behave identical, why because CoCodeCustID maps to part of a primary key, and a primary key cannot hold a null value. But now let us look at a more complex requirement.

Each customer can have two representatives, so again we have two ways to do this. Let us look at the first way, where we share the Co field on the relationships.

The only catch in doing this is that the fields FirstContactRepID and SecondContactRepID can never be NULL!!!!! Why, because Access implements a FULL MATCH on relationships! So unless you want to make all foreign keys mandatory you need to abandon this approach in favour of the following, where we have separate fields for recording the Co value for each foreign key; this is very flexible, but also a bit messy to deal with.

This approach for Access works well, because you are no longer compelled to enter a value in the fields FirstContactRepId or SecondContactRepId, because we have now uncoupled the Co value into CoFirstContactRepID and CoSecondContactRepId.

Conversion to SQL Server and Extra Flexibility

If we convert everything to SQL Server, then because SQL Server implements RI to allow part of a key to be completed without fully validating the key, we can support the following (which Access can not support):-

So what does this all mean?

Well you can build a multi-tenanted solution where either all companies are completely isolated, or you can allow some sharing of data across the tenants. Access only supports total isolation, but SQL Server supports both models.

Download Code

The sample code can be downloaded from here for Access 2007/2010 Databases and SQL Server.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page