List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 18 2005 5:50pm
Subject:one product in more categories
View as plain text  
Hi to all!

I have tables products and categories

CREATE TABLE 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 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,
  product_includes TEXT NULL,
  product_catalog VARCHAR(45) NULL,
  product_status ENUM(0,1) NULL,
  product_supplier VARCHAR(45) NULL,
  product_start_date DATE NULL,
  product_exp_date DATE NULL,
  product_on_sale ENUM(0,1) NULL,
  PRIMARY KEY(product_id),
  INDEX ac_products_index1(product_start_date, product_exp_date),
  INDEX ac_products_index2(product_status),
  INDEX ac_products_index_onsale(product_on_sale)
);

Since one product can be in more then one category, is it correct to 
create thirs table with those info?

CREATE TABLE ac_products_categories (
  pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
  p_id INTEGER(6) UNSIGNED NULL,
  c_id INTEGER(6) UNSIGNED NULL,
  PRIMARY KEY(pc_id),
  INDEX pc_index(p_id, c_id),
);

Or, there is better solution?

Thanks!

-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