CODEWITHSUNDEEP
sqlsql-server
NetPowerON
NetPowerON

Reputation: 13

How to Foreign Key reference two tables where those tables themselves have a PK FK relationship?

I wasn't sure how to ask this question and it's likely a simple answer.

I have two tables like this.

Office
------
PK OficeId
   OfficeName

Location
--------
PK LocationId
FK OfficeId

And one table like this where I store related information.

PurchaseOrder
-------------
PK PurchaseOrderId
FK OfficeId
FK LocationId

So my database will contain a list of Offices that have locations. That's a 1 Office to multiple locations. Office could be company or what ever.

So when I insert a purchase order I wanted to store the OfficeId really for convience, but the OfficeId and LocationId must be a configuration that would satisfy the 1 to many relationship in the other tables. I understand that I could simply just store the LocationId and join inside a query and I might end up doing this, however wanted to ask some experts about this first.

I'm using SQL Server 2012. Is there a constraint to do this without creating a trigger that will simply check if that's a valid configuration?

Upvotes: 0

Views: 137

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Over the years, I've found that when any modeling decision is made "for convenience", it ends up being a mistake.

In your model, PurchaseOrder relates to Location which relates to Office. Shortening the relation of PurchaseOrder directly to Office may work for a while. Then, years later, a minor restructuring of the company ends up reassigning the Texas location from the MidWest office in Illinois to the Western office in New Mexico.

So you (or your predecessor) updates the Location table. But now all kinds of reports and queries are returning wrong information. Maybe it turns out to be easy enough to find and fix the problem, but why open yourself up to such situations in the first place?

If you want convenience, use a method that doesn't leave you vulnerable to future anomalies due to changes in the data. In this instance, create a view which shows PurchaseOrders with Location and Office using the proper references. Now when the office changes, the view will show the new office and every report and query based on the view works as expected.

Views are great for just this kind of use. Just don't be manipulating the underlying model for trivial purposes. Your driving goal in modeling the database is Data Integrity. Sorry, but Ease Of Use is way down at the very bottom of the priority list.

Upvotes: 0

John Rix
John Rix

Reputation: 6703

As you acknowledge yourself and as pointed out by the commentors, the OfficeID in the PurchaseOrder table is redundant and de-normalises your data model. Unless you have a burning need to keep it there, I would remove it and just do the appropriate joins when you need to get the office associated with the PurchaseOrder. Foreign keys and indexing will take care of this efficiently.

If you really want to keep the column though and ensure referential integrity, perhaps this is the answer you're looking for: Multiple-column foreign key in MySQL?

Upvotes: 1

Related Questions