List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 18 2005 6:20pm
Subject:Re: one product in more categories
View as plain text  
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 
>  
>
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