tabledesign suggestions for costs-table for several customer/destinations/loads?

[Glenn]

Hi all,

I would like to hear your suggestions on Access table design for the
following situation:

A shipping company has a costs sheet for each of their customers
containing the shipment cost of goods to several destinations.

an example:
Customer 1: Jones Co.

Destination 1: Paris
1 crate: 50$
2 crates: 90$
3 crates: 140$
1 cubic m: 75$
10 cubic m: 500$
....

Destination 2: Rome
1 crate: 80$
2 crates: 140$
3 crates: 210$
....

The number of possible destinations varies from customers to customers,
as do the destinations themselves.
Also the type/number of goods can be different from customer to
customer.

As a basic model, I see an Excel-type worksheet per customer with as
columns the destinations for that customer and as rows the type of goods
to be shipped. The cell values would represent the shipment costs.

What would be an efficient table design taken into account that the
number of destinations and types of goods is highly variable per
customer?

Thanks for your insights
glenn

 

[Glenn]

One way I could do the table design, is:

Tables:
Customer: Primary key: CUS -customer info
Destination: Primary Key: DEST -destination info
TypeGoods: Primary Key: TYPE -Type of goods ( crate, cubic m,...)

Cost table:
Primary Key: CostID
Foreign Keys: CUS, DEST, TYPE
Other Fields:
Quantity
Cost

I could add another table to manage subsets of destinations per customer
- not all destinations are possible for each customer.

the same information would also be reused on the individual waybills.

suggestions/remarks/remakes welcome
glenn