List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 20 2005 8:35pm
Subject:Re: one product in more categories
View as plain text  
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

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