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