How to enforce inte

Posted on 2016-09-08
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 14

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 14

Expert Comment

by:Megan Brooks
ID: 41790255
I will have another look at this when I am free.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 14

Accepted Solution

Megan Brooks earned 500 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 47

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 14

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 14

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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