Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Overriding derived values

Re: Overriding derived values

From: dawn <dawnwolthuis_at_gmail.com>
Date: 29 Nov 2005 12:47:16 -0800
Message-ID: <1133297236.856345.23320@z14g2000cwz.googlegroups.com>

Ira Gladnick wrote:
> 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.

Other than the use of the numerical codes (why not text codes?) I agree with your redesign. One question -- does the owner of the system ever want the discount to be overridden for more than a month? For example, in your scenario where someone has stolen, it seems like there should be a DENY code on this bowler indefinitely -- until it is changed. There is no reason to set this each month and plenty of reason not to introduce a possible failure by insisting it be set each month.

>
> This way, once a bowler has been either allowed a special dispensation
> or specifically denied the discount, their special status is perfectly
> clear.

It makes sense to me that you want to know whether an operator has changed the status or the status is purely derived from the other data.  At the very least, the value should not be automatically populated. In the current scenario, it could be populated with true or false only when overridden and generated from the data otherwise (which, I suppose, gets into a question of null-handling).

> 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.

I agree. The current design is poor.

> The designer of the system maintains that since the current data entry
> person knows her customers well enough, this shouldn't be a problem.

Because she will be there forever, right?

> The design is that GrantDiscount column would be automatically set
> based on the bowler's average,

It is not a good practice to start a value as derived and then permit the modifications. Permit the derived value to stand on its own as derived, and then collect the other data about exceptions for a bowler from the data entry person.

> 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.

If you like booleans, you could have attributes of

alwaysDiscount and neverDiscount. These would both be set to false and the user could turn either one to true at any point, but not both. The single attribute you describe which can be DEFAULT, FORCE, or DENY sounds good to me.

> 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?)

If not, we could write one. Cheers! --dawn Received on Tue Nov 29 2005 - 14:47:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US