Click here to get back home

Database schema ideas
 Home | NewsGroups | Search | About
Login Password
Register Now! Lost Password?
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
Database schema ideas effect1 11-25-2005
Get Chitika Premium
Posted by effect1 on November 25, 2005, 9:10 am
Please log in for more thread options
 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?


Posted by jerry gitomer on November 25, 2005, 10:13 am
Please log in for more thread options
 effect1 wrote:
> 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?
>

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.

HTH

Jerry

Posted by Bill Karwin on November 25, 2005, 1:47 pm
Please log in for more thread options
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.

Posted by effect1 on November 26, 2005, 4:31 pm
Please log in for more thread options
Thank you both for your advice. :)



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

Similar ThreadsPosted
Database table row has all of its fields "shuffle" on it - ideas? May 18, 2006, 4:04 pm
Database design (need help with a schema) August 20, 2006, 3:43 am
Any Ideas about that ... June 16, 2005, 8:09 am
MySQL stops functioning periodically - any ideas? July 26, 2006, 10:42 pm
Question about schema October 28, 2006, 8:51 pm
Copying a table from one schema to another January 29, 2006, 11:41 pm
partial schema retrieval November 18, 2006, 4:05 pm
adding contraints FK refs to existing schema April 19, 2006, 11:25 pm
converting ms access database to mysql 5 database March 13, 2006, 5:33 pm
Two way syncronizing with a website database with a local database February 4, 2007, 4:34 pm

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap