Database schema ideas

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
My company sells widgets.  In our 'shopping cart' that we're building,
we want to list out the price for each widget.  The problem is that
the price varies depends upon which widget package (out of 10) as user
selects, as well as which website (out of 8) they visit.  So, 80
different possible prices per widget (and there are lots of different
kinds of widgets).

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?

Re: Database schema ideas

effect1 wrote:
Quoted text here. Click to load it

You really don't want to do that.  The reason is the maintenance
involved if you have to support additional packages and or
websites in the future.  (Sure, I know that they swore on a
stack of bibles that would never happen, but ....)

The best solution is to have a price table where each row has
three columns; package, website, price.



Re: Database schema ideas

jerry gitomer wrote:
Quoted text here. Click to load it

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

Bill K.

Re: Database schema ideas

Thank you both for your advice.  :)

Quoted text here. Click to load it

Site Timeline