List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 19 2005 7:11pm
Subject:Re: one product in more categories
View as plain text  
No. It doesn't work. First, I found one error: there are two columns for 
same thing in ac_products ac_products_product_id and product_id.
Second, ac_extended_prices table doesn't fit with multiple solutions

:(




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