List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 18 2005 6:03pm
Subject:Re: one product in more categories
View as plain text  
"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 
Thread
one product in more categoriesafan@afan.net18 Oct
  • Re: one product in more categoriesSGreen18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
      • Re: one product in more categoriesPeter Brawley18 Oct
        • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesPeter Brawley19 Oct
          • Re: one product in more categoriesSGreen19 Oct
            • Re: one product in more categoriesafan@afan.net20 Oct
              • Re: one product in more categoriesPeter Brawley20 Oct
                • Re: one product in more categoriesafan@afan.net20 Oct
                  • Re: one product in more categoriesPeter Brawley21 Oct
              • Re: one product in more categoriesSGreen21 Oct
                • Re: one product in more categoriesafan@afan.net21 Oct
                  • Re: one product in more categoriesSGreen21 Oct
    • Re: one product in more categoriesRhino18 Oct
  • Re: one product in more categoriesRhino18 Oct
    • case insensitive searchafan@afan.net19 Nov
      • Re: case insensitive searchJasper Bryant-Greene19 Nov
        • Re: case insensitive searchafan@afan.net20 Nov
          • Re: case insensitive searchBjörn Persson21 Nov
      • Re: case insensitive searchScott Haneda19 Nov
      • Re: case insensitive searchBjörn Persson19 Nov