jerry gitomer wrote:
Does anyone have any recommendations on how to build a database that
will help prevent a separate record for each of the 80 different
possible prices for each widget?
The best solution is to have a price table where each row has three
columns; package, website, price.
I agree with Jerry, the best solution is to list the prices per
package/website combination. Actually, it'd be per
widget/package/website combination.
You might have a situation today where you can describe some formula in
each case ("website 31's prices are always .97 * retail price") so you'd
be tempted to store simply a percentage factor per website. But in my
experience, these sorts of things change constantly. Any formula you
might devise to simplify it will become inadequate.
The people who set the prices simply don't think in terms of automation
and conformance to simple rules and formulas. For instance, they'll
want a few individual widgets to have a lower price during a promotion.
Or one website will want their price to match another website's price,
or at a factor of .97, whichever is lower. And so on.
The possible "rules" for pricing are unpredictable and too complex to
make formulas for. So you're better off just storing them and providing
a nice web interface for an administrator to edit them quickly.
Include in this user interface some way to apply changes to multiple
packages or multiple websites, so you can do changes in groups (for
instance, show a list of widgets with checkboxes next to them. Add two
multi-select menus, one for packages and one for websites. The
combination of checkboxes by the widgets and the selected items in the
two select-menus defines to which rows to apply the changes). That will
reduce the potential maintenance burden of having so many records per
widget.
Regards,
Bill K.