Solved

How to enforce inte

Posted on 2016-09-08
8
69 Views
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:

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

data
0
Comment
Question by:canuckconsulting
[X]
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
8 Comments
 
LVL 15

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?
0
 

Author Comment

by:canuckconsulting
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.
0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41790255
I will have another look at this when I am free.
0
Independent Software Vendors: 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!

 
LVL 15

Accepted Solution

by:
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.
0
 
LVL 50

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.
0
 
LVL 15

Expert Comment

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

Author Closing Comment

by:canuckconsulting
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.
0
 
LVL 15

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.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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?
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

738 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