One to many relationship with a composite key table

Question

There may be a better way to do this, but here it is

I am trying to record shipping prices

The elements are

• shipping zone
• shipping weight
• 3 carriers UPS, USPS, FEDEX
• 7 Zones 2 through 8

Table 1 UID Zone (PK) Weight (PK) *so you can't create a duplicate combination"

Table 2 UID UPS Price USPS Price FEDEX Price

How do I add the 3 entries to each combo in in Table 1

Hopefully I explained that well

Not sure how to add a one to many relationship with the composite key table

Or if there is a better way to do this

Answer 1

It looks like you are still working on Table Normalization. In that case here is an example:

-------------------------------------------
|     CarrierID      |    CarrierName     |
-------------------------------------------
|                  1 | UPS                |
-------------------------------------------
|                  2 | USPS               |
-------------------------------------------
|                  3 | FEDEX              |
-------------------------------------------

sql:

SELECT Carriers.CarrierName, Zones.ZoneName, ShippingCostFormulas.ShippingCostFormulaName, ShippingCosts.Weight, ShippingPrice([Weight],[ZoneID],[CarrierID]) AS ShippingPrice
FROM Carriers INNER JOIN (Zones INNER JOIN (ShippingCostFormulas INNER JOIN ShippingCosts ON ShippingCostFormulas.ShippingCostFormulaID = ShippingCosts.ShippingCostFormulaID) ON Zones.ZoneID = ShippingCosts.ZoneFK) ON Carriers.CarrierID = ShippingCosts.CarrierFK;

'results:
--------------------------------------------------------------------------------------------------------------------------------
|    CarrierName     |      ZoneName      |        ShippingCostFormulaName        |       Weight       |     ShippingPrice     |
--------------------------------------------------------------------------------------------------------------------------------
| UPS                | two                | FixedPrice                            |                0.2 |                 $0.10 |
--------------------------------------------------------------------------------------------------------------------------------
| USPS               | three              | FixedPrice                            |                0.3 |                 $0.17 |
--------------------------------------------------------------------------------------------------------------------------------
| FEDEX              | not not four       | FixedPrice                            |                0.3 |                 $0.19 |
--------------------------------------------------------------------------------------------------------------------------------
| UPS                | two                | ByWeightZoneCarrier                   |                0.2 |                 $0.10 |
--------------------------------------------------------------------------------------------------------------------------------
| USPS               | three              | ByWeightZoneCarrier                   |                0.3 |                 $0.17 |
--------------------------------------------------------------------------------------------------------------------------------
| FEDEX              | not not four       | ByWeightZoneCarrier                   |                0.3 |                 $0.19 |
--------------------------------------------------------------------------------------------------------------------------------

'formulas:
Public Function ShippingPrice(weight As Double, zone As Long, carrier As Long) As Currency

If carrier = 1 Then
ShippingPrice = weight * 0.5
End If
If carrier = 2 Then
ShippingPrice = weight * 0.4 + 0.05
End If
If carrier = 3 Then
Dim price As Currency
price = weight * 0.3 + 0.1
If zone = 1 Or zone = 5 Then
price = price + 0.2
End If
ShippingPrice = price
End If
End Function

The Tables need to hold our old data and be able to handle new data. We don't have full control of Carrier, Zones, and ShippingPriceFormulas hence we need type tables to future proof the database for when ScroogeMcDuck Shipping uses it's own zones and doubles the shipping price. No need for a composite key. I'm going to declare the zone-weight part of the question as too vague and guess that you should Consider A data entry form and Data Validation. Replacing a straight calculated field with a public function like ShippingPrice allows leveraging the full power of VBA and a whole lot more for just a little extra complexity.

The example table structure already needs improvement as weight should be in a packages table and ShippingDate will be important and isn't directly modeled. Instead ShippingDate is hidden behind making new ShippingPrice rules over time.

it looks like you wanted to compare rates between shippers. Normally you use the more powerful forms and reports to interact with the data, but a query is good enough for an example: grab and rearrange the data you need to either show or calculate with and then show the results of the calculations as well,

*Ignore the fixedPriceFunction I lost it on the cutting room floor, edit: put ShippingPrice in a code module