UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Constrain Two Foreign Keys That Are Related To Each Other, Access 2016    
 
   
Fridgebird
post Dec 8 2017, 07:03 AM
Post#1



Posts: 17
Joined: 5-April 17



Hi all, am I going about this the best way?

I have three tables like so:

Style (StyleID PK, various other fields)
RequisitionUnit (RUID PK, styleID FK, and others)
Version (versionID PK, styleID FK, and others)

So Style is related one-many to RU and to Version.

Now I need to add a CommercialModel table, where records are related to specific RUs and specific version of the same style.

CommercialModel (CMID PK, RUID FK, VersionID FK, and others)

For each record in the commercial model, the RU and version have to be from the same style. How do I create this constraint in Access? I guess, since each commercial model record can only be related to one style (i.e. the style that its RU and version both belong to) StyleID also has to be a FK in the CM table too.

CommercialModel (CMID PK, StyleID FK, RUID FK, VersionID FK, and others).

Or is there a better way to handle this? It looks a bit like I'm doing something superfluous by connecting CM to style and to RU (when each RU is already related to only one style), but I can't figure out a better way to do it.

Opinions gratefully received

Dan
Go to the top of the page
 
GroverParkGeorge
post Dec 8 2017, 09:59 AM
Post#2


UA Admin
Posts: 33,794
Joined: 20-June 02
From: Newcastle, WA


What is the relationship, if any, between a "RequisitionUnit" and a "Version"?

Can you create a RequisitionUnit that has no version associated with it, or vice versa?

Perhaps, a verbal explanation of the business being modeled would help us understand better how these entities relate, or should relate, to one another. Not in database terms, but as you explain it to a lay person.
Go to the top of the page
 
Fridgebird
post Dec 8 2017, 11:08 AM
Post#3



Posts: 17
Joined: 5-April 17



A "style" is a product, for instance jam or a cereal bar. A "requisition unit" is a format in which we buy it, for instance a jar or a case of jam; or a box or a case of cereal bars. A "version" holds a lot of the information about the recipe and labelling of the product. For instance, version 1 might be organic and version 2 might be non-organic.

The commercial model holds all the cost and RRP info. So each record in commercial model refers to a certain version and a certain requisition unit. For instance:

CMID 1, jar of jam, jam version 1, £1.00
CMID 2, case of jam, jam version 1, £6.00
CMID 3, jar of jam, jam version 2, £1.10
CMID 4, case of jam, jam version 2 £6.80
CMID 5, case of cereal bars, cereal bar version 1, £10
etc.

Does that make sense?
Go to the top of the page
 
HairyBob
post Dec 8 2017, 11:18 AM
Post#4



Posts: 992
Joined: 26-March 08
From: London, UK


As George says, we really need more information regarding the buisness model.

Having said that, you could do this with a validation rule in the CommercialModel table which disallows data entry / edit at the table level if the StyleID foreign key constrained to the StyleID primary key of the RequisitionUnit table is not the same as the StyleID foreign key constrained to the StyleID primary key of the Version table (see attachment).

Assumptions:
(1) For each style, there must be both an associated Requisition Unit and an associated Version (if not, you won't be able to insert a CommercialModel record).
(2) The combination of Requisition Unit and Version for a particular style must be unique in the Commercial Model table (I've created a unique index in the CommercialModel table to enforce this - of course, if that's not the case, you simply don't put in the unique index).

HTH...

Hairy.
Attached File(s)
Attached File  ConstrainRelatedForeignKeys.zip ( 19.94K )Number of downloads: 5
 
Go to the top of the page
 
Fridgebird
post Dec 11 2017, 04:06 AM
Post#5



Posts: 17
Joined: 5-April 17



Ah, that's an interesting idea. I'll see if I can make that work, thanks very much for taking the time to respond :-)
Go to the top of the page
 
HairyBob
post Dec 11 2017, 11:15 AM
Post#6



Posts: 992
Joined: 26-March 08
From: London, UK


yw.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 06:58 AM