----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, October 18, 2005 4:26 PM
Subject: Re: one product in more categories
> 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?
> :)
>
Your guess at the solution is probably usable but it could cause
difficulties that can be avoided with a different design.
You could do what you want in a single table. It would look like this:
PRICE Table (Primary Key: Product_ID + Quantity)
=======
Product_ID Quantity Regular_Price Sale_Price
------------ --------- ---------------- -------------
1 25 1.59 0.99
1 50 1.39 0.99
1 100 1.19 0.99
1 200 0.99 0.99
2 25 3.99 -
2 50 3.59 -
The dash in the sale price is used to denote a null. As you probably know, a
null is a special value that means "unknown or not applicable". Here, a null
in a sale price means that there is no sale price for this combination of
product_ID and quantity; people have to pay the regular price.
I should mention one other possibility that you may want to consider in your
design.
In the example you've given, the sale price was the same for that product
regardless of the quantity; I'm guessing that is NOT the normal situation
and that sale price varies with quantity most of the time, at least
somewhat. For example, the sale price might be 0.99 for up to 100 items but
then 0.79 for quantities over 100. The design I just stated should work fine
for that case. However, if the sale price was always the same for a given
product regardless of the quantity, you could do the table a bit
differently: omit the Sale_Price column and create a row that was
specifically for the sale price; it could use a reserved quantity like 0 to
indicate that it was a sale price row:
PRICE Table (Primary Key: Product_ID + Quantity)
=======
Product_ID Quantity Regular_Price
------------ --------- ----------------
1 25 1.59
1 50 1.39
1 100 1.19
1 200 0.99
1 0 0.99
Here, the last row indicates that the sale price for Product 1 is 0.99
regardless of the quantity. This makes the table a bit smaller but still
shows all the data. You just have to remember that your query always
searches for a quantity of 0 to obtain the sale price.
Rhino
>
>
> [EMAIL PROTECTED] wrote:
>
> >"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 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
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.12.0/134 - Release Date: 14/10/2005
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.0/134 - Release Date: 14/10/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
