Re: one product in more categories

[[EMAIL PROTECTED]]

Oops! My bad! :)

original name for categories and products are  ac_categories and 
ac_products. but I wanted to "simplify" and deleted "ac_" part - but not 
on all places. And my "simplified" query become "mess" query. Sorry.

But, you got a point. And I got the answer.

And "ac_products_categories" table doesn't have any other association. 
It doesn't matter who, when, why added a prodcut to particular category.


Thanks Shawn.




[EMAIL PROTECTED] wrote:

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/18/2005 01:50:20 PM:

 

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


   


I think you may have just one too many columns. I can understand c_id and 
p_id. Those point to categories.cat_id and products.product_id. I 
understand creating a column to identify the association (pc_id). But what 
is the column ac_products_product_id for?

I would have probably defined it this way

CREATE TABLE ac_products_categories (
       p_id int unsigned not null,
       c_id int unsigned not null,
       PRIMARY KEY(p_id, c_id),
       INDEX(c_id, p_id)
);

The PK ensures that each product can only be associated with any category 
only once. The other key makes reverse lookups blindingly fast (if you 
know the category and want a list of all of the products). Forward lookups 
are covered by the PK.

I didn't identify the association with it's own column because there is 
nothing else this association carries with it (no other data ABOUT the 
association). If you wanted to add something like who assigned this 
product to this category or what date it was added, then I may have left 
it in. 

To answer your bigger question, "YES!"  This is a proper way of creating a 
many-to-many relationship in MySQL. Good job!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]