Dear Vixens and Reynards:

I have a confession to make. I have a client billing app where I
am using the DBMS as a data store. I was youngish, I was foolish, and
there was the press of time. Despite the evil I have loosed upon the
world, the app has run reasonably well for the required purposes.

Business has changed, and it is now time to examine how things
are done. Who knows? Maybe this time, I will be able to do it right.

We have some unusual requirements for lookups. I need help.

For a given transaction of work done, the work order number has
to be determined and the correct rate row has to be determined. Both
of these of messy. I give the latter. I may inflict the other one
later.


A transaction has a work function code (the activity), client
code, a work classification code (the kind of activity), and a
transaction date. It also has dependent columns such as various
charges. Selection of the correct rate row is as follows:

Is there one rate row matching the work function code, client
code, and work classification code that is valid on the transaction
date? If so, use it and done. If multiple rows, error.

Failing that, is there one rate row matching the work function
code and client code and having a blank work classification code that
is valid on the transaction date? If so, use it and done. If
multiple rows, error.

Failing that, is there one rate row matching the work function
code and having a blank client code and a blank work classification
code that is valid on the transaction date? If so, use it and done.
If multiple rows, error.

Failing that, error.


How should I normalise this? I admit to having all of the rate
rows together regardless of blankness of work classification codes and
blankness of client codes.

How do I do lookup?

The nasty questions: If I add or change rate rows, how do I
ensure that the work transactions are correctly updated? If I try to
delete a rate row, how do I ensure that the work transactions are
correctly updated or that the operation is disallowed (if it would
invalidate an existing transaction)?

Sincerely,

Gene Wirchenko