List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 18 2005 9:58pm
Subject:Re: one product in more categories
View as plain text  
afan,

 >For the same project (below) I have problem with building table for 
product prices.
 >In "regular" online store, price is usually part of the products table.
 >But, I need a solution for multiple prices. E.g.
 >QTY -    25        50       100       200
 >Price -   $1.59   $1.39   $1.19   $0.99

 >Also, if product is On Sale I need to be shown both prices: regular 
and sale price
 >QTY -    25        50       100       200
 >Price -    $1.59   $1.39   $1.19   $0.99
 >Sale -     $0.99   $0.99   $0.99   $0.99

First two footnotes to the excellent advice offered by Rhino & Shawn on 
your categories, products & products_categories tables:

1. It will be best to type the primary & foreign keys identically--all 
unsigned, or all not.

2. To avoid rounding errors, use DECIMAL rather than FLOAT for money 
columns.

Before you model extended price computations, you have to ask & answer 
crucial questions:
(i) do you know in advance all the kinds of price extensions that can 
come up?
(ii) do you want the price rules to be (a) in the database or (b) in the 
app?
(iii) if the answer to (ii) is (a), do you want the rules in stored 
procedures, or in tables which application code must parse?
(iv) does the app need to track price history (eg so it can recreate a 
price computation from six months ago)?

Suppose the answers are those that most conventionally apply: only qty 
and sale will ever come up, the rules will be in the app, and you can 
leave history to the backups. Then you can take a very simple, 
semi-normalised approach (leaving out some details):

CREATE TABLE extended_prices (
  epid INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  qty_up_to SMALLINT NOT NULL,
  price_per DECIMAL(10,2) NOT NULL,
  price_per_sale DECIMAL(10,2) NULL
);

which permits you to enter whatever (qty cutoffs, price, sale) combos 
are desired for any desired products, and find them for any product with 
a very simple query. There is a risk, though: in six months the client 
may find that new price extensions are needed, and/or that she needs 
history after all.

Now, add the wrinkles that other possible, but presently unidentified 
price extensions (eg 'special promotions', 'coupons', &c) will be 
required, and that history must be tracked. Now you need at least, again 
normalising only partly...

CREATE TABLE pricemodtypes (
  pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(20)
)

CREATE TABLE extended_prices (
  epid INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  pricemodtype_id INT NOT NULL,
  qty_up_to SMALLINT NOT NULL,
  begindate DATE NOT NULL,
  enddate DATE NOT NULL,
  price_per DECIMAL(10,2) NOT NULL,
  price_per_mod DECIMAL(10,2) NULL
);

the query to retrieve all the prices for a product is more complicated 
but still straightforward.

Of course wrinkles multiply as if conjured by a Sorcerer's Apprentice. 
Perhaps we should pause here for a breath. Is this the info you need?

PB



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005

Thread
one product in more categoriesafan@afan.net18 Oct
  • Re: one product in more categoriesSGreen18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
      • Re: one product in more categoriesPeter Brawley18 Oct
        • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesPeter Brawley19 Oct
          • Re: one product in more categoriesSGreen19 Oct
            • Re: one product in more categoriesafan@afan.net20 Oct
              • Re: one product in more categoriesPeter Brawley20 Oct
                • Re: one product in more categoriesafan@afan.net20 Oct
                  • Re: one product in more categoriesPeter Brawley21 Oct
              • Re: one product in more categoriesSGreen21 Oct
                • Re: one product in more categoriesafan@afan.net21 Oct
                  • Re: one product in more categoriesSGreen21 Oct
    • Re: one product in more categoriesRhino18 Oct
  • Re: one product in more categoriesRhino18 Oct
    • case insensitive searchafan@afan.net19 Nov
      • Re: case insensitive searchJasper Bryant-Greene19 Nov
        • Re: case insensitive searchafan@afan.net20 Nov
          • Re: case insensitive searchBjörn Persson21 Nov
      • Re: case insensitive searchScott Haneda19 Nov
      • Re: case insensitive searchBjörn Persson19 Nov