List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 21 2005 1:19pm
Subject:Re: one product in more categories
View as plain text  
Thanks Shawn.
See  comments:

SGreen@stripped wrote:

>Sorry I didn't get back to you earlier today. I have had a busy day. 
>Comments embedded...
>"afan@stripped" <afan@stripped> wrote on 10/20/2005 04:35:30 PM:
>
>  
>
>>Could you please tell me should this work?
>>
>>CREATE TABLE categories (
>>  cat_id INTEGER(8) UNSIGNED 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,
>>  PRIMARY KEY(cat_id)
>>)
>>TYPE=InnoDB;
>>
>>    
>>
>Good. I would add another INDEX for (cat_parent, cat_id) to speed up 
>subcategory listings.
>
>  
>
>>CREATE TABLE products (
>>  prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
>>  prod_no VARCHAR(12) NOT NULL,
>>  prod_name VARCHAR(45) NOT NULL,
>>  prod_description TEXT NULL,
>>  prod_colors TEXT NULL,    // since there are tons of colors, defined 
>>differently by different suppliers, I had to go this way for color 
>>    
>>
>options
>  
>
>>  prod_includes TEXT NULL,
>>  prod_catalog VARCHAR(45) NULL,
>>  prod_status ENUM('0','1') NOT NULL DEFAULT 0,
>>  prod_supplier VARCHAR(45) NULL,
>>  prod_start_date DATE NULL,
>>  prod_end_date DATE NULL,
>>  PRIMARY KEY(prod_id),
>>  INDEX products_index1(prod_status),
>>  INDEX products_index2(prod_start_date, prod_end_date)
>>)
>>TYPE=InnoDB;
>>    
>>
>
>Good again.
>if you don't want your application to parse out a list of colors from the 
>colors field, you will need a table of just colors and another association 
>table between colors and products.
>  
>
This is why I made color columns this way.
Product 07V0128: Neutrals - White(30n)Heathers - Ash (93h), Sport 
Grey (95h)Colors - Gold (24c), Navy (32c), Forest Green (33c), Black 
(36c), Orange (37c), Red (40c), Royal (51c), Lt Blue (69c), Purple 
(81c), Maroon (83c)
Product 07V0205: Neutrals: **White, Natural. Lights: *Ash, Graphite, 
**Light Steel. Darks - **Black, Bluestone, Cardinal, Copper, Daffodil 
Yellow, Denim Blue, Gold, Gold Nugget, Kelly Green, Light Blue, Lime, 
Moss, *Navy, Orange, Pink, Pebble, Pine, Purple, *Red
Product 07V0560: Apricot, Burnt Orange, Butter, Cedar, Celery, Chalky 
Blue, Chalky Mint, Chalky Purple, Charcoal, Cigar, Columbia Blue, 
Crimson, Dandelion, Denim, Dijon, Dorm Green, Forest, Grasshopper, Hot 
Red, Indigo, Kelly, Key Lime, Latte, Lime, Mustard, Nantucke
Since, there is NO RULE for colors, this is a only solution, right?

>Remember that the optimizer won't use an index if it thinks that the index 
>will return over 30% or so of the records in the table. With only two 
>values in it, an index on prod_status (all by itself) will probably never 
>have enough cardinality to be useful. Consider using it as part of a 
>compound index instead.
>  
>
Looks like I'm doing wrong for a loooong time :)
Ok, then. Do I have to Index prod_status or it's not necessary?
Could you please give me more info on "Consider using it as part of a 
compound index instead"?


>  
>
>>//    since one product could be in more then one category, I created 
>>this assoc. table
>>CREATE TABLE categories_has_products (
>>  categories_cat_id INTEGER(8) UNSIGNED NOT NULL,
>>  products_prod_id INTEGER(8) UNSIGNED NOT NULL,
>>  PRIMARY KEY(categories_cat_id, products_prod_id),
>>  INDEX categories_has_products_FKIndex1(categories_cat_id),
>>  INDEX categories_has_products_FKIndex2(products_prod_id),
>>  FOREIGN KEY(categories_cat_id)
>>    REFERENCES categories(cat_id)
>>      ON DELETE NO ACTION
>>      ON UPDATE NO ACTION,
>>  FOREIGN KEY(products_prod_id)
>>    REFERENCES products(prod_id)
>>      ON DELETE NO ACTION
>>      ON UPDATE NO ACTION
>>)
>>TYPE=InnoDB;
>>    
>>
>
>OK, your primary key acts as an index for categories_cat_id and for 
>(categories_cat_id, products_prod_id). The second index on just 
>categories_cat_id is redundant and can be deleted with no side-effects.
>
>  
>
>>//    table methods represent different kind of imprints on apparels. 
>>same an apparel with different kind of imprint could have a different 
>>    
>>
>price.
>  
>
>>CREATE TABLE methods (
>>  met_id INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
>>  met_name VARCHAR(12) NULL,
>>  PRIMARY KEY(met_id)
>>)
>>TYPE=InnoDB;
>>
>>    
>>
>So far, you are doing a good job at normalizing.
>
>  
>
>>CREATE TABLE prices (
>>  price_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
>>  products_prod_id INTEGER(8) UNSIGNED NOT NULL,
>>  methods_met_id INTEGER(4) UNSIGNED NOT NULL,
>>  qty INTEGER(8) UNSIGNED NULL,
>>  price DECIMAL(10,2) NULL,
>>  sale_price DECIMAL(10,2) NULL,
>>  PRIMARY KEY(price_id),
>>  INDEX prices_index1(qty),
>>  INDEX prices_FKIndex1(methods_met_id),
>>  INDEX prices_FKIndex2(products_prod_id),
>>  FOREIGN KEY(methods_met_id)
>>    REFERENCES methods(met_id)
>>      ON DELETE NO ACTION
>>      ON UPDATE NO ACTION,
>>  FOREIGN KEY(products_prod_id)
>>    REFERENCES products(prod_id)
>>      ON DELETE NO ACTION
>>      ON UPDATE NO ACTION
>>)
>>TYPE=InnoDB;
>>
>>
>>Only thing that bothers me is start and end dates for sale prices:
>>shirt: available from today until 4/1/2006
>>price: $7.95
>>on sale from 11/15/2005 to 1/15/2006
>>sale price: $5.95
>>
>>Right now, with "my" solution, administrator has to turn on/off "on 
>>sale". I need to automate this by start and end dates for sale.
>>Any ideas?
>>
>>Thanks for any help.
>>
>>-afan
>>
>>    
>>
>Since each price (a product-method-quantity combination) can have several 
>SALE prices (each with different date ranges) you need to normalize them 
>to their own table.
>
>CREATE TABLE sales_prices (
>        sale_id int auto_increment,
>        price_id int not null,
>        saleprice,
>        startdate,
>        enddate,
>        PK,
>        FK,
>        FK
>)
>
>That way each Price can have a corresponding sale price for a particular 
>range of dates. When that range expires, the sale price no longer matches 
>your query and you stop showing it on the site. That's how I would solve 
>that particular issue.
>  
>
:) I did it that way and then deleted the table because it looked to me 
like two same tables (prices and sale_prices) doing same thing...


>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>  
>
Thanks Shawn!

-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