From: afan@afan.net
Date: October 18 2005 8:26pm
Subject: Re: one product in more categories
List-Archive: http://lists.mysql.com/mysql/190544
Message-Id: <43555A8B.5080909@afan.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

For the same project (below) I have problem with building table for 
product prices.
In "regular" online store, price is usually part of the products table.
But, I need a solution for multiple prices. E.g.
QTY -    25        50       100       200
Price -   $1.59   $1.39   $1.19   $0.99

Also, if product is On Sale I need to be shown both prices: regular and 
sale price
QTY -    25        50       100       200
Price -    $1.59   $1.39   $1.19   $0.99
Sale -     $0.99   $0.99   $0.99   $0.99

What would be structure of "Quantity" and "Price" tables?

My guess:

CREATE TABLE ac_quantities (
  quantity_id INT(8) NOT NULL AUTO_INCREMENT,
  quantity INTEGER(6) NOT NULL,
  product_id INTEGER(8) NOT NULL,
  PRIMARY KEY(quantity_id),
  INDEX ac_quantities_index1(quantity)
);


CREATE TABLE ac_prices (
  price_id INT(8) NOT NULL AUTO_INCREMENT,
  price FLOAT(10,2) NOT NULL,
  product_id INTEGER(8) NOT NULL,
  product_type ENUM('regular','sale') NOT NULL DEFAULT 'regular',
  PRIMARY KEY(price_id),
  INDEX ac_prices_index1(price)
);



How "close" am I?
:)




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