[Last Call] Learn how to a build a cloud-first strategyRegister Now


How to enforce inte

Posted on 2016-09-08
Medium Priority
Last Modified: 2016-09-10
I'm trying to work out a way to prevent against a data integrity issue we've identified in our SQL Server 2012 DB.  We implement the logic in the application, but I wanted to know if there was a way to lock it down at the DB.

Consider a business which provides our company with licenses we can print on our products.  A product is always associated with one of these businesses.  A product can optionally be associated with one of that businesses' licenses.

For example, consider the Business Marvel who provides us Licenses "Spiderman" and "Iron Man".  We could print a Product (tshirt) with just "Marvel" on it (Business: Marvel, License: Null) or a Product with "Spiderman"  on it (Business: Marvel: License: Spiderman).  

Here's the schema:

The issue is how to ensure that a product associated with one business is not associated with a license for a another business.

Question by:canuckconsulting
  • 5
  • 2
LVL 16

Expert Comment

by:Megan Brooks
ID: 41790207
Let me make sure I understand. You have a parent entity, Business (Business Unit) and an optional child entity, License. You want to be able to associate a third entity, Product, with a Business and optionally with a License.

If License were not optional, you could simply make Product a child of License. You could also define a "No License" License row for each Business that needs it in order to make this work, but then you would need to ensure that only one row had the flag set for each Business ID.

If you want to keep Product as an association between Business and License, however, you can. Assuming that you want each combination of Business ID and License ID to be unique for License, you could define a unique constraint for those columns on License. You could then relate Product to License using both those columns in the FK instead of just License ID.

I feel like there should be a simpler solution, but the relationships here are a little unusual and I haven't had to work this one out before. Does this look like a step in the right direction?

Author Comment

ID: 41790221
Hi rscowden

Yes, I think you have it.  Both a Product and a License must have a parent business.  A Product may optionally have a parent License but, if it does, the License Business parent and Product Business parent should be the same.

This actual sprang from an earlier Expert Exchange Question.  I was hoping there was  a better way to deal with it than expanding the foreign key.  It just feels wrong.  In fact, if it comes down to that being my only choice I'd probably just leave it as something to be handled by code.
LVL 16

Expert Comment

by:Megan Brooks
ID: 41790255
I will have another look at this when I am free.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 16

Accepted Solution

Megan Brooks earned 2000 total points
ID: 41790379
It seems like an FK defined against a unique constraint would be the fastest-executing solution, but it does introduce another index and the associated storage.

If insert performance for Product rows is not critical, another possibility might be using an INSTEAD OF trigger. I really don't like triggers, for all the problems I have seen them cause over the years, but an INSTEAD OF is cleaner than an AFTER trigger - no rollback is required. It cancels the original query, and the trigger will have to re-execute it once the key validation has been performed successfully. You would need to intercept both INSERT and UPDATE.
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41791051
You database model doesn't allow a Product to have more than one Business License, i.e., a Product (t-shirt) can have "Marvel: Spiderman" and "Marvel: Ironman". Is really that the intention? If it is then table Product is missing the BussinessID (NOT NULL).
Btw, your License table is missing the LicenseID. It only has BussinessID.
LVL 16

Expert Comment

by:Megan Brooks
ID: 41791553
Some IDs are missing from the schema. They are all shown in the diagram.

Author Closing Comment

ID: 41792583
Thanks Megan.  I think your FK suggestion really is the ticket.  

I've gotten into the habit of always having an Identity PK and using that alone as my FK.  I love the cleanness and flexibility of it (ANYTHING can change and the FK stays valid) but I'm letting my fussiness get in the way of what is clearly a better approach.
LVL 16

Expert Comment

by:Megan Brooks
ID: 41792703
I gave quite a bit of thought to how to do this, because it was a type of relationship I hadn't encountered before. You need to relate Product to both Business and License, while factoring in the relationship between those two tables as well, and you can't create an FK that references two tables, so you need to have a unique constraint on License ID that also relates it to Business ID at the same time. You can't add the Business ID to the License PK, because that would allow duplication of the License ID.

The extra unique constraint feels redundant, since there is already a PK-FK relationship between Business and License, but it isn't used here to enforce uniqueness. Instead, it serves to 'relay' the Business key to the Product-License relationship, so that both the Business and License PKs are able to participate in the Product FK relationship. The result is a single, fast, FK B-tree lookup that serves to protect both Product-License and Product-Business.

FKs are designed both for referential integrity and for speed, so while conceivably the same thing could be accomplished in a less efficient manner, using a secondary lookup to save storage, there is no option I can see to make the FK less efficient and you would be stuck with using triggers, and it would take two of them to have the equivalent of the unique constraint.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question