Overriding derived values

A design problem in my organization similar to the following has
arisen. (The example below is a bit contrived, as I would prefer not
to discuss our exact situation).

Say that, once a month, a bowling alley employee enters league players
averages into a data entry screen. After entering all averages, a
report is generated for the alley snack shop showing all players who
are entitled to a 15% discount on beer purchases.

If a bowler's average is above 200, s/he is normally entitled to the
15% discount.

The bowling alley can also choose to grant the 15% discount to bowlers
whose average falls below 200, on a case-by-case basis (e.g., to the
owner's daughter, etc.).

Additionally, the alley can also choose to deny the 15% discount to
certain bowlers whose average is above 200 (e.g., to a bowler
previously caught stealing hamburgers from the snack shop, etc.)

The designer of this system has chosen to create a boolean column
called GrantDiscount in the Bowler table. Only bowlers who have this
column set to True will appear in the monthly discount report.

When an average above 200 in entered, this column will automatically be
set to True. Likewise, it will automatically be set False when an
average below 200 is entered. The user can also manually set this
column to true via a data entry field for bowlers below the 200
average, and can manually set it to false (for hamburger thieves and
other miscreants) for bowlers whose average is above 200.

My feeling is that a more suitable design would be to eliminate the
GrantDiscount column, and instead create a DiscountOverride column that

can take three possible values:
0 - Default. Grant beer discount only if average is above 200
2 - Force. Grant discount regardless of the bowler's average.
3 - Deny. Deny discount regardless of the bowler's average.

This way, once a bowler has been either allowed a special dispensation
or specifically denied the discount, their special status is perfectly
clear. In the original design, there would appear to be some
uncertaintly as to how to handle the situation when a bowler's average
crosses the 200 threshold in either direction, as it could be
potentially unknowable as whether the GrantDiscount column had been set
manually (perhaps several months earlier) and should continue in its
present state regardless of current average, or if crossing the 200
boundary should automatically cause a change to GrantDiscount.

The designer of the system maintains that since the current data entry
person knows her customers well enough, this shouldn't be a problem.
The design is that GrantDiscount column would be automatically set
based on the bowler's average, and that each month it would be up to
the entry operator to reset GrantDiscount manually as appropriate. But
my feeling is that a) why introduce potential problems where they are
not necessary?, and b) what happens when the entry operator leaves the
bowling alley and is replaced by a newcomer? Basically, it doesn't
seem desirable to create a column whose semantics are unclear, based on
the assumption that the user of the system can assign the proper
meaning based on information extrinsic to the system itself.

I would very much appreciate:

1) Any comments on the two designs outlined above, or possible
alternate designs.

2) Any possible references to scenarios of this kind in published
database design literature. (Is this possibly an example of a
well-known design consideration that I might be unaware of, such as
Don't-Allow-Entry-Into-a-Derived-Column?)

.

Relevant Pages

  • Derived column design question
    ... are entitled to a 15% discount on beer purchases. ... The bowling alley can also choose to grant the 15% discount to bowlers ... My feeling is that a more suitable design would be to eliminate the ... potentially unknowable as whether the GrantDiscount column had been set ...
    (comp.databases.theory)
  • Re: Calculated Value will not store in Table
    ... I have another design question that has to do with table ... I have a combo box in frmPOItem that lists MaterialGrp ... >> and then it becomes 0 so there is no discount applied. ... >design that DOES NOT store these values. ...
    (microsoft.public.access.forms)
  • Re: Calculated Value will not store in Table
    ... If we use option Explicit, then we MUST define all variables we use, and any ... > had difficulty with discount. ... design that DOES NOT store these values. ...
    (microsoft.public.access.forms)
  • Re: Overriding derived values
    ... > averages into a data entry screen. ... > are entitled to a 15% discount on beer purchases. ... > The bowling alley can also choose to grant the 15% discount to bowlers ... > My feeling is that a more suitable design would be to eliminate the ...
    (comp.databases.theory)
  • Re: Derived column design question
    ... >My feeling is that a more suitable design would be to eliminate the ... Grant discount regardless of the bowler's average. ... >potentially unknowable as whether the GrantDiscount column had been set ... >the entry operator to reset GrantDiscount manually as appropriate. ...
    (comp.databases.theory)