Database Reference
In-Depth Information
Reviewing the Data and New Business Rules
You've decided to start reselling personal security products to your customers. To help keep
track of things, you have a Products table in your database. The table has these fields:
▪ A text field called SKU (a retail term for a product ID code)
▪ A text field called Description
▪ A number field called Cost
▪ A number field called Price
Switch to that layout now and flip through the product records. To help drive sales to your
larger clients, you want to implement a volume discount scheme; they should get a 5 percent
discount if they buy enough. But some of the products you sell don't have enough markup to
justify these discounts. You want to be sure the discount never reduces your markup below
20 percent.
First, the lawyers say you have to add a line to all your marketing materials: “Volume dis-
counts not available for all products.” You can make this line as small as humanly possible,
and hide it way down in the corner. Next, you need to fix your database so it tells you the
discounted price for each product.
Planning the Calculations
To implement this discount scheme, take what you need to know and translate it into calcula-
tion terms.
▪ First, calculate 95 percent of the price (a 5 percent discount):
Price * .95
▪ Second, you also know the cost (in the Cost field) and you can figure out the lowest price
by adding 20 percent to this cost:
Cost * 1.2
▪ Finally, the discounted price is either the calculated discount price,
or
the cost + 20 per-
cent price, whichever is
greater
. Put another way, you want the
maximum
of these two
values:
Max ( Price * .95 ; Cost * 1.2 )
Using the
Max()
function, the previous calculation results in either the discounted price or
the minimum price, whichever is greater (see the box on
The Max() and Min() Functions
).
That result is
almost
perfect. But suppose you have a product whose
normal
price is less than
