afanafan.net
Date: Tue Oct 18 2005 - 12:50:20 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]