Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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 52

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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