CODEWITHSUNDEEP
sql-serverdatabaseentity-frameworkt-sql
Daniel A. Burke
Daniel A. Burke

Reputation: 554

Need Help Creating Primary Key/Foreign Key Relationships between Multiple Tables

Background:

(I'm using Microsoft SQL Server 2014)

My company receives data files (tblFile) that contain many accounts (tblAccount). For each data file, we may perform multiple "pricings" (tblPricing), and these "pricings" may contain all of the accounts in the file, or only a subset of them, but the "pricings" cannot contain any accounts not in the file from which the pricing is based. So, in summary:

  1. We get a single file
  2. This file can contain many accounts
  3. We create many pricings from this single file
  4. Each pricing can contain all or some of the accounts from the file it is linked to, but no accounts not in that file

Here is a (way) simplified database diagram as it exists today: Simplified Database Diagram

Problem:

What's working so far:

Our problem comes from trying to enforce integrity between the subset of accounts that a file has and the subset of accounts that a pricing has. With the above structure, the tblPricingAccounts can contain accounts not contained in the tblFileAccounts, violating our need for each pricing to contain only the accounts from the file of which it is based upon.

I've tried changing the foreign key relationships where I broke the link between tblPricingAccounts and tblAccount, removed 'acct_id' from tblPricingAccounts, and instead linked tblPricingAccounts to tblFileAccounts (yes, I know I need a primary key in tblFileAccounts and I had one). But, then I was able to insert whatever 'pricing_id' I wanted into tblPricingAccounts. Now I could link accounts to a pricing that had nothing to do with the file that originally contained those accounts.

Need

At the end of the day, I don't care what the structure or relationships of my database look like. I simply need the following criteria met, and I can't seem to wrap my mind around it:

  1. A file contains many accounts.
  2. A file contains many pricings.
  3. A pricing contains many accounts, but those accounts MUST be contained in the file that the pricing is linked to.

Any help is appreciated, and I'm open to all suggestions that can be performed within SQL Server. Ultimately I'm building a web application around this database, and I'm using Entity Framework 6 to make life easier (mostly...). I could obviously enforce the above 3 needs through my code, but I really would like the database to be the last line of defense in enforcing this integrity.

Upvotes: 0

Views: 199

Answers (2)

Stuart J Cuthbertson
Stuart J Cuthbertson

Reputation: 438

This is a situation that foreign key constraints are not intended to handle. FK constraints test for existence of values between tables; they do not enforce particular cardinality requirements.

Simple cardinality is the "one to many", "many to many" relationships mentioned in the question. Your more complex need is still essentially about cardinality though: it's a requirement that certain subsets of rows relate to certain other subsets of rows in a particular way. "Windowed cardinality" if you will. (My own coinage as far as I know.)

As suggested in comments to the question, one way to enforce this wholly within the database is via triggers. A well crafted trigger in this case would probably test whether new rows to be inserted are valid, and erroring without insertion if not. For a bulk insert, you may wish to insert valid rows and throw the rest, or throw everything back if 1+ rows are invalid. You can also craft logic to handle updates or deletions that could break your integrity requirements.

Be aware that triggers will negatively affect performance, especially if the table is being changed frequently.

Other approaches are to handle this in application logic, as suggested, and/or allow data into the tables regardless, but validate existing data periodically. For example, a nightly process could identify data failing this requirement and pass to a human to correct.

Upvotes: 1

Stuart J Cuthbertson
Stuart J Cuthbertson

Reputation: 438

It sounds like tblFileAccounts might be superfluous. Try removing it altogether and inferring which accounts exist in which files through the relationships captured in tblPricingAccounts and tblPricing.

If this meets your need, and there are no attributes (columns) which rightfully belong to the tblFileAccounts object (table), then I think your problem is solved.

Upvotes: 1

Related Questions