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

Options: ReplyQuote


Subject
Written By
Posted
help in database design
ang
May 15, 2006 02:08AM
September 19, 2007 05:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.