many_to_many relationship advice

[reb0101]

hey all,
I am really hoping someone can help me out on this one since I have been banging my head against the wall for hours.
It's a many-to-many relationship problem that I hope can be resolved in the simplest terms.
I will try and keep from being too long winded here and keep it brief.

I am trying to develop a database for shipping.
The user will first select a carrier (Shipper) and that is one table, we'll call it tbl_CARRIER and here are four typical records for a Carrier:

tbl_CARRIER
AALV
ACOA
APLS
AROF

the Carrier table will have name of Carrier like above for one field and a CARRIER_ID which is primary key, so only two fields in that table, so far so good.

Once a Carrier is selected then they pick a Container Type.
Here are few, actually all records for Container Type:

tbl_CONTAIN_SIZE
40 FT OVER
40 FT UNDER
Doubledrop/RGN
Doubledrop/RGN OOG
Flatbed/Stepdeck
Flatbed/Stepdeck OOG

Same here, an ID autonumber Primary key and name of Container.
I have a one to many relationship set up here because Carrier AALV for example might have all these containers or only 2 or 3.

tbl_CARRIER
carrier_ID

tbl_CONTAIN_SIZE
contain_ID
carrier_ID
CONTAINER

carrier_ID is Primary and Foreign Key on tbl_CONTAIN_SIZE

Here's where it gets tricky.
There is one additional table, a Mileage table.
There is a Start Miles and End Miles and then a charge per mile.
Here is an example of that table.

START_MILES END_MILES RATE_MILE
51 75 $3.86
76 100 $3.21
101 150 $2.57
151 200 $2.82

The miles go on for a lot of records.
all the way up to like 3000 miles and above

Here's a normal selection.
User picks say ACOA from above and then they pick Flatbed/Stepdeck for a Container size.
Please keep in mind the following;
There are 6 different types of container sizes and each of them have their own mileage rates like above.
The way I have it (in my head) is one Shipper (ACOA for example) selects one of many Container sizes (6 to choose from) and that container size will have it's on rate records like above but ACOA will have different rates per mile for a 40 FT OVER Container than APLS will have for the same 40 FT for the same miles.
does anyone have an idea how to do a many-to-many relationship on all of this?
Any and ALL help will be very much appreciated!
Thanks in Advance.

 

[Beetle]

I don't think I would have the Container Size table directly related to the Carrier table in this scenario. As you stated, this is a many-to-many relationship, so it needs a junction table. Presumably you have a table for shipping records to record where the shipment is going, etc. It would seem to me that this table would effectively serve as the junction table. For each record in the shipping table you would, among other things, select a Carrier and a Container and enter (or calculate) the mileage.

If your Mileage Charge table also stored CarrierID and ContainerID in each row, then you would simply look up the appropriate charge based on the Carrier, Container and mileage of the current shipping record. In a case like this, I would think that you would want to store this looked up value in the shipping table for historical purposes.

 

[reb0101]

Sean,
thank you for your prompt reply.
It's late so I will try and 'process' what you replied with after what I hope will be a god night sleep, but you mentioned something that I hope to have already done; that being where from and to the shipments will go.
That part I seemed (or hope) to have worked out.


REGION COUNTRY PORT CARRIER
Africa Algeria Algiers, Algeria MAEU


The user picks Region and through a cascading combo box it will isolate the countries for that region
In the example above, there are many countries in Africa but in this example they selected Algeria and then from there the Port.
You can see the Carrier to the far left.


As they select REGION it will insert that value into another table.
Same with COUNTRY and PORT.
They will have an Origin and Destination, both pulling from the same values, REGION, COUNTRY, PORT.
It was once they selected the CARRIER that then caused the confusion on how to create a 'Link' or 'Join' Table for CARRIER, CONTAINER SIZE and MILEAGE.
I'll try again in the morning but thanks for your reply