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