Help with db setup and relationships

[Mike G]

I need some of the expertise in this forum.

I'm trying to setup a database that will be somewhat similar to the Business Tracker in theory but extended somewhat. I have customers and products and invoicing and payments. This is a port operation that has ships that come into different docks for different customers and either load or unload certain chemicals (products). What I don't know how to do is set up the rates for charging the customers. We charge two items... wharfage and dockage. Wharfage is based on the amount of product loaded or unloaded, i.e. 4.76 cents per barrel for oil and 2.13 cents per barrel for all other chemicals. Dockage is based on the length of the vessel a the dock. This is where I'm stumped. If a vessel is 0' - 199' the fee is $1.40 per foot, if it's 200' - 299' it's $1.62 per foot, 300' - 399' it's 1.79 per foot... and so on up to 900' - 1000'. I have no clue how to design a fee structure to accomodate this. What I wanted was a table where I could assign the rates and have them calculated according to the length and the amount of product moved. I also want to be able to update rates but have previously billed transactions keep the old rate at which they were billed and not updated when the rates table is changed. Hope this makes sense and that someone can give me some ideas.... Thank You

[LiveOak]

In all the relational tutorials then talk a lot about minimizing duplicate storage of data, but seldom about cases in which you need to use stored data instead of related field. For instance, the customer's address on an invoice must be stored, not related data. The address is part of the historical record and should reflect the customer's address at the time the invoice was cut.

You have a similar case, you need to lookup the needed data from the rate table into the line item record corresponding to invoice. As an alternative, you can keep all past rates in the rate table and don't view them for new invoices, but maintain the connection to give the proper rates for old invoices.

If you setup your rate table with a text string "200'-299'" indexed as ASCII (FM6) or Unicode (FM7), you can use this to relate to the rate entry and bring across the rate of $1.62. Again, this can be done as a lookup. If you want to keep all rates in the rate table, you will need additional informtion to determine which version of the 200'-299' rate to use.

-bd