Derived column design question

From: <>
Date: 29 Nov 2005 10:47:00 -0800
Message-ID: <>

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.

In order to further my discussions with the designer of this system, 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, which I might be able to show to the system designer. (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?)
Received on Tue Nov 29 2005 - 19:47:00 CET

Original text of this message