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

 ><snip> But, Peter's 2nd part is actually "touching" the change in the 
project:
 >product can have more then 2 prices. E.g. if you select shirt with your
 >logo embroidered - it's one price. If your logo will be screened on the
 >shirt - other price. And then if the shirt is on sale - 2 more prices 
Total
 >4 different prices have to be shown on catalog.

Right, you have to treat price extension types as data, as suggested, 
but already you have the previously unanticipated wrinkle that multiple 
extensions can apply to one item, eg silkscreen the logo or embroider 
it  and/or it is on sale .... And you can be entirely sure that more 
wrinkles are on the way.

That's where the question of rules comes in. Suppose when you arrive at 
work tomorrow, you're told that the system has to learn how to price 
some screened names by the letter and others by a single price for a 
whole name. To avoid having to write a lot of awkward literal code for 
all such wrinkles, you need a way of storing such rules in the 
pricemodtypes table. A simple pricemodtype example might be name='logo', 
method='screen', price_type='per letter', unit_price=whatever; another 
might be 'screen+sale'; another might be name='logo', method='embroider, 
on sale', price_type='whole', unit_price=whatever. And so on. You know 
this detail, I don't. Here is one general approach: (i) collect all the 
price mods the boss can tell you about, and turn them into the smallest 
possible set of parameteristed formulas, (ii) create procemodtypes 
columns for all the parameters you need, (iii) write generic code which 
simply reads the rules and computes the parameterised prices, (iv) test 
the result with the boss to ensure that you have all his rules right, 
then (v) tell the boss that in the future, his rules have to fit into 
those params or he has to pay for big app enhancements.

The alternative is to code every subtype literally in pricemodtypes.

Only you have heard all the specs, only you have talked with your boss, 
so only you know what the details are going to be, and which if any can 
be parameterised as above.

PB

-----

afan@stripped wrote:

> Thanks guys for really detailed answers.
>
> After your emails I talked to project supervisor and found that there 
> is "some changes" in the project:
> (i) do you know in advance all the kinds of price extensions that can 
> come up?
> - I hope I know them now :(
>
> (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?
> - Those two I really didn't get. If you thought on this: there is no 
> rule in making prices for different number of items in pack. next 
> price is NOT for x% lower or for $x lower. There is no rule. If you 
> were thinking on something else please explain. Thanks.
>
> (iv) does the app need to track price history (e.g. so it can recreate 
> a price computation from six months ago)?
> - This would be actually more online catalog where visitor/customer 
> will create an inquiry. And we don't need to track a purchase history 
> in this case.
>
> But, Peter's 2nd part is actually "touching" the change in the 
> project: product can have more then 2 prices. E.g. if you select shirt 
> with your logo embroidered - it's one price. If your logo will be 
> screened on the shirt - other price. And then if the shirt is on sale 
> - 2 more prices Total 4 different prices have to be shown on catalog.
> The solution:
>
> 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
> );
>
> will be fine?
>
> Actually, there is what I have for the moment for my DB:
>
> categories and subcategories:
> CREATE TABLE ac_categories (
>  cat_id INT(6) NOT NULL AUTO_INCREMENT,
>  cat_name VARCHAR(45) NULL,
>  cat_description TEXT NULL,
>  cat_parent INTEGER(4) UNSIGNED NULL,
>  cat_status ENUM('0','1') NULL DEFAULT 0,
>  PRIMARY KEY(cat_id),
>  INDEX ac_categories_index1(cat_status)
> );
>
> CREATE TABLE ac_products (
>  product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
>  product_no VARCHAR(12) NULL,
>  product_name VARCHAR(45) NULL,
>  product_description TEXT NULL,
>  product_colors TEXT NULL,   // since there is hundreds of different 
> colors and color combination, we will have colors as description
>  product_includes TEXT NULL,   // shows what is includes in price (e.g 
> how many colors for logo and how much costs additional color)
>  product_catalog VARCHAR(45) NULL, // products are in groups of 
> catalogs - for internal use
>  product_status ENUM('0','1') NULL, // is product available (visible 
> at front end)
>  product_supplier VARCHAR(45) NULL,
>  product_start_date DATE NULL,
>  product_exp_date DATE NULL,
>  PRIMARY KEY(product_id),
>  INDEX ac_products_index1(product_start_date, product_exp_date),
>  INDEX ac_products_index2(product_status),
> );
>
>
> since, one product can be in more than one category:
> CREATE TABLE ac_products_has_ac_categories (
>  ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
>  ac_categories_cat_id INT(6) NOT NULL,
>  PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
>  INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
>  INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
> );
>
> CREATE TABLE ac_extended_prices (
>  epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
>  ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
>  ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
>  product_id INTEGER(8) UNSIGNED NULL,
>  pricemodtype_id INTEGER(8) UNSIGNED NULL,
>  qty_up_to INTEGER(8) UNSIGNED NULL,
>  begindate DATE NULL,
>  enddate DATE NULL,
>  price_per DECIMAL(10,2) NOT NULL,
>  price_per_mod DECIMAL(10,2) NULL,
>  PRIMARY KEY(epid),
>  INDEX ac_extended_prices_index_date(begindate, enddate),
>  INDEX ac_extended_prices_index_qty(qty_up_to),
>  INDEX ac_extended_prices_FKIndex1(ac_products_product_id),
>  INDEX ac_extended_prices_FKIndex2(ac_pricemodtypes_pricemodtype_id)
> );
>
> CREATE TABLE ac_pricemodtypes (
>  pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
>  name CHAR(40) NULL,
>  PRIMARY KEY(pricemodtype_id)
> );
>
>
>
> Your opinion?
>
> Thanks for help.
>
> -afan
>
>
>
> Peter Brawley wrote:
>
>> 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.4/143 - Release Date: 10/19/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