Rate tables

[larochy]

I'm working on building a database for our accounting team and they want to
use it to track Services revenue. The customer buys an amount of consulting
$'s and they either are bound to our "standard rates" or can negotiate the
rates we charge for Engineers, Consultants, etc. One of their requests is to
create/store standard or custom rates for each Project/Invoice and when the
hours are entered into the system for each role, they run up against these
custom rates to determine the usage against their balance of consulting $'s
they purchased upfront. I guess my question lies in the fact that I can't
figure out how I would store the custom rates for each order in a table. Any
suggestions? Here's what it currently looks like but it's going to be
unwieldy to enter these for a thousand or more invoices each month. I would
rather they enter in the rates against the hours in the Excel upload but
they're insisting the rates be stored in the database. Is this common to
store rates like this in a database?

ProjectID C_ID CC Consultant Sr_Cnslt Proj_Mgr Trainer
ABIO0004 1969 100 200 200 225 220
ABQS0006 2558 100 200 200 225 220
ABQS0007 2558 100 200 200 225 220
ABQS0008 2558 100 200 200 225 220
ABQS0009 2558 200 1000 1000 1200 1150

 

[John W. Vinson]

I'm working on building a database for our accounting team and they want to
use it to track Services revenue. The customer buys an amount of consulting
$'s and they either are bound to our "standard rates" or can negotiate the
rates we charge for Engineers, Consultants, etc. One of their requests is to
create/store standard or custom rates for each Project/Invoice and when the
hours are entered into the system for each role, they run up against these
custom rates to determine the usage against their balance of consulting $'s
they purchased upfront. I guess my question lies in the fact that I can't
figure out how I would store the custom rates for each order in a table. Any
suggestions? Here's what it currently looks like but it's going to be
unwieldy to enter these for a thousand or more invoices each month. I would
rather they enter in the rates against the hours in the Excel upload but
they're insisting the rates be stored in the database. Is this common to
store rates like this in a database?

ProjectID C_ID CC Consultant Sr_Cnslt Proj_Mgr Trainer
ABIO0004 1969 100 200 200 225 220
ABQS0006 2558 100 200 200 225 220
ABQS0007 2558 100 200 200 225 220
ABQS0008 2558 100 200 200 225 220
ABQS0009 2558 200 1000 1000 1200 1150

Not in a properly normalized database, no. What if you add a new rate
category? Add a new field to your table, change all your queries, change all
your forms, change all your reports!? Ouch!

"Fields are expensive, records are cheap". Rather than a spreadsheet design
like this consider having a normalized set of tables:

RateTypes
RateType Text <primary key> (e.g. Consultant, Senior Consultant, ...)

BaseRates
RateType Text <link to RateTypes>
BaseRate Currency <the default rate for this service>
EffectiveDate Date/Time
EndDate Date/Time

ProjectRates
ProjectID <link to the Projects table primary key>
RateType <link to Rates>
BilledRate Currency <filled in with VBA code in a form from BaseRates, and
available for modification if you choose to charge project ABQS0009 a 500%
premium for being such pains in the neck)