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