List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 21 2005 12:40am
Subject:Re: one product in more categories
View as plain text  
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.

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.

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

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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