"[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
