Forum Discussion
MS Access Many Junction Tables - Design Advice
Hi everyone, I hope everything is safe at your side. I'm trying to figure out in a model of relationship that could work for my case. Here is the situation:
I have a tbPurchaseRequest that will have a tbPurchaseRequestDetails with the items' list (coming from tbMaterials). In one purchase request, I can have up to 3 Suppliers (stored in tbSupplier). The prices changes per supplier for each request, so, I need to capture the price for that specific request. Here are my doubts:
1. Where is the best table to capture the quantity requested and the price information?
2. What is the most logical way to organize this?
Any advice?
You are right. A properly normalized table design doesn't lend itself to that multicolumn interface layout. You could use a crosstab query to achieve the layout, but then it would not be editable. Another option is to go to an unbound form based on that layout, and use VBA to write the values back into the various tables.
However, the most familiar way to accomplish it is one that is illustrated in this demo on my website.
9 Replies
As a general rule, I avoid any suggestion that one solution is "the best". All too often that depends on a number of factors, and any solution has to be evaluated in context. What is "best' in one situation may or may not be the "best" in a different situation. So let's talk about the standard, ore most often recommended approaches,
Quantity requested is a fact about one specific request. Therefore, it belongs in the tbPurchaseRequestDetails table as part of each line item.
Price is more complicated (see above comments on "best" solutions). If you need to capture "Price BY Supplier AS OF purchase", and you want that to be part of the record for a purchase, then it too is a fact about one specific request, and it too belongs in the same tbPurchaseRequestDetails table. IMO that is.
There are other ways to capture the "Price as of" value, including a more complicated query that looks at the date of a purchase and at the "effective date" of a price for a material for a supplier. That might be considered a more standard approach from the perspective of Normalization. It would mean that you don't store the price in two places. However, in many cases, it's a lot easier to sacrifice a bit of purity in Normalization and have the "Price BY Supplier AS OF purchase" value stored with that purchase for historical purposes.
Again, it depends, in part, on your situation and your requirements. That's how I would probably do it, though.
Thanks for you reply. I've tried to create the relationships based in the concept above. Could you kindly check if is it correct?
looking my tbRequestDetails, it seems quite repetitive. Am I doing a wrong way to key in the information?
I think there is still some modification needed, assuming I understand properly what you need.
Again, the assumption is that you want the "AsOfRequestDate" Price to be associated with the material purchased, and that Price is specific to one particular Supplier for that particular Material in effect as of that date. Note that you must store the Price somewhere else and that isn't shown in this screenshot.
Therefore, if I were to do this, I would have another table that joins suppliers and materials. It is also a junction table with FIVE fields.
SupplierMaterialID (AutoNumber PK)
SupplierID FK from Supplier
MaterialID FK from Material
CurrentPrice This supplier's price for this material
AsOfPriceDate The date this price is effective for this material for this supplier.
Instead of joining both materials and suppliers to the tblRequestDetails table, you would only need the SupplierMaterialID (which identifies the two other values, MaterialID and SupplierID) and the CurrectPrice, determined as the price effective on the most recent AsOfPriceDate.
