0

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
NetPowerON
  • 13
  • 3
  • 1
    I think storing 'LocationId' is the only way to implement your 'PurchaseOrder' table and keeping your DB Normalised – Rahul Bhati Feb 27 '15 at 11:02
  • Add FK into `PurcheseOrder` table as `OfficeID` only.no need to add `LocationId`because we get the `PurcheseOrderID` we can find the office.then we check `Location` table with that `office ID` we can find the location – Elshan Feb 27 '15 at 11:02
  • 2
    Storing `OfficeID` in `PurchaseOrder` is redundant, since `OfficeID` is functionally dependent on `LocationId` then you can just obtain office ID by joining to Location. You *could* create a unique constraint on `Location` (LocationID, OfficeID), then create a foreign key on PurchaseOrder that references this constraint, but I really feel this is introducing more problems than it solves. – GarethD Feb 27 '15 at 11:05
  • @GarethD. Having the unique constraint on LocationID and OfficeID would only make them unique so it would be possible to add an location that doesn't belong to that particular office only because they both exist. Just wanted to consult and see if this was something someone else solved. Turns out the best way is to just use the LocationID like everyone suggests. Thanks. – NetPowerON Feb 27 '15 at 20:30
  • It is not the unique constraint that ensures that you can't add a purchase order to a location that doesn't exist, it is the foreign key that the unique constraint makes possible – GarethD Feb 28 '15 at 21:07

2 Answers2

1

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?

Community
  • 1
  • 1
John Rix
  • 6,271
  • 5
  • 40
  • 46
0

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.

TommCatt
  • 5,498
  • 1
  • 13
  • 20