thoughts on table setup

One approach may be to have a "FeeStructure" table, organized like so:

tblFeeStructure
----------------
FeeStrutureID (autonumber, PK)
FeeStructureDescription (name or description of this fee structure)
FlatRateUnit (page, docuement, etc)
FlatRateQuantity (numeric)
FlatRateFee (numeric or currency)
PerItemUnit (page, document, etc)
PerItemFee (numeric or currency)

The FeeStructureID would then go into your tblContractorFee as another FK.

You could even break this new table into 2 tables.. one for FlatRate fees
and one for PerItem fees. It depends on what your data needs are. From the
few times I've dealt with fee structures, one table would have sufficed.

This may not be the exact approach you want or need, but it may serve as a
starting point. If you have more complex fee structures (like 'per item'
prices that change as the quantity increases), you'll need something
different.

Hope this helps,

Anne

"Andromeda" <tuoni42@hotmail.com> wrote in message
news:c35615de.0406180644.144345c1@posting.google.c om...[color=blue]
> I'm drawing a bit of a blank on the proper way to set up one specific
> table.
>
> I need to enter the fees and additional charges for people my company
> farms out work to across the US seperated by county. So far I have:
>
> tblContractorFee
> - FeeID (autonumber, PK)
> - Contractor ID # (FK)
> - Job Type ID # (FK)
> - County ID # (FK)
> - Job Price
> - Per (job/hour/document/etc.)
> ** here's where I need the help **
> - Copy Price
> - Per (page/document/etc)
> **
> - Mileage Charge
> - Per (mile/flat fee/etc)
>
> Copy charges usually come in a number of different ways. Per page and
> per document are the most common, but a lot of counties are now
> charging weird things like $10 for the first page and $2 each
> additional page per document. Then there are the contractors that
> include the first 5 pages in the job price, but charge for anything
> over that. I'm drawing a blank on the easiest way to set this up
> without having a billion columns. Thoughts?[/color]