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.
SGreen@stripped wrote:
>"afan@stripped" <afan@stripped> 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
>
>
