help in database design
Posted by:
ang
Date: May 15, 2006 02:08AM
I am working with a shipping system.
Need help to design some of the tables for its tariff module.
Tariff involved Ocean Freight (OCB), surcharges and add-on charges.
I provide sample data so that you guys can help in designing the table stuctures.
Below are sample data for OCB:
Ocean freight depends on the origin, destination (usually a baseport in a trade/region) and cntr type.
(column header)
origin, trade, destination, movement term, transshipment port, cntr type, cost rate, tariff, effective date, expiry date
(data)
mypkg, eur, deham, cy-cy, direct, 20gp, 800, 850, 01/05/2006, 31/05/2006
mypkg, eur, deham, cy-cy, direct, 40gp, 1500, 1600, 01/05/2006, 31/05/2006
mypkg, eur, deham, cy-cy, direct, 40hc, 1600, 1700, 01/05/2006, 31/05/2006
mypkg, eur, nlrtm, cy-cy, direct, 20gp, 800, 850, 01/05/2006, 31/05/2006
mypkg, eur, nlrtm, cy-cy, direct, 40gp, 1500, 1600, 01/05/2006, 31/05/2006
mypkg, eur, nlrtm, cy-cy, direct, 40hc, 1600, 1700, 01/05/2006, 31/05/2006
mypkg, abc, egxyz, cy-fo, egdam, 20gp, 600, 650, 15/05/2006, 15/05/2006
mypkg, abc, egxyz, cy-fo, egdam, 40gp, 1200, 1300, 15/05/2006, 15/05/2006
mypkg, abc, egxyz, cy-fo, egdam, 40hc, 1300, 1400, 15/05/2006, 15/05/2006
mypen, eur, deham, cy-cy, direct, 20gp, 850, 900, 01/05/2006, 31/05/2006
mypen, eur, deham, cy-cy, direct, 40gp, 1550, 1650, 01/05/2006, 31/05/2006
mypen, eur, deham, cy-cy, direct, 40hc, 1650, 1750, 01/05/2006, 31/05/2006
Below are sample data for surcharge:
Surcharges are the rate applies to the destination.
For the same origin, destination and trade as in OCB, surcharge are applied as follow:
mypkg, eur, deham:
mypkg, eur, nlrtm:
surcharge: BAF -- 200 per TEU, 01/05/2006, 31/05/2006
- 20gp is count as 1 TEU while 40gp and 40hc is count as 2 TEU
- so BAF for mypkg, eur, deham, 20gp = 200, while 40gp/40hc = 400
mypkg, eur, deham:
surcharge: CAF -- 5.4% of OCB, 01/05/2006, 31/05/2006
- means CAF for mypkg, eur, deham, 20gp = 843.20 (OCB = 800), 40gp = 1581 (OCB = 1500), 40hc = 1686.40 (OCB = 1600)
mypkg, abc, egabc:
surcharge: BAF -- 100 per TEU, 01/05/2006, 31/05/2006
surcharge: CAF -- 5.4% of OCB, 01/05/2006, 31/05/2006
surcharge: ISPS -- 150 per BOX*, 01/05/2006, 31/05/2006
* per BOX = per container
- means ISPS 20gp = 150, 40gp = 150, 40hc = 150
Below are sample date for add-on charges:
Add-on charges are additional charges apply when shipment is beyond baseport, eg:
From mypkg to deham and then to noosl
- add-on charges is the rate from deham to noosl
deham, eur, noosl, cy-cy, direct, 20gp, 350, 350, 01/05/2006, 31/05/2006
deham, eur, noosl, cy-cy, direct, 40gp, 550, 550, 01/05/2006, 31/05/2006
deham, eur, noosl, cy-cy, direct, 40hc, 650, 650, 01/05/2006, 31/05/2006
deham, eur, deabc, cy-fo, direct, 20gp, 250, 250, 01/05/2006, 31/05/2006
deham, eur, deabc, cy-fo, direct, 40gp, 450, 450, 01/05/2006, 31/05/2006
deham, eur, deabc, cy-fo, direct, 40hc, 550, 550, 01/05/2006, 31/05/2006