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

The model is progressing. The test for how well it works is what happens 
when you ask it to represent all presently known types of products. What 
happens when you hae to apply two 'methods' to one product?

PB

-----

afan@stripped wrote:

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


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/145 - Release Date: 10/20/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