! 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 (AT) unimin (DOT) com wrote:

>"afan (AT) afan (DOT) net" <afan (AT) afan (DOT) netwrote on 10/18/2005 01:50:20 PM:
>

>
>>Hi to all!
>>
>>I have tables products and categories
>>
>>CREATE TABLE categories (
>cat_id INT(6) NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
>p_id INTEGER(6) UNSIGNED NULL,
>c_id INTEGER(6) UNSIGNED NULL,
>PRIMARY KEY(pc_id),
>INDEX pc_index(p_id, c_id),
>>);
>>
>>, 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 ABUT 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

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 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) NT NULL AUTINCREMENT,
quantity INTEGER(6) NT NULL,
product_id INTEGER(8) NT NULL,
PRIMARY KEY(quantity_id),
INDEX ac_quantities_index1(quantity)
);

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

How "close" am I?
:)

SGreen (AT) unimin (DOT) com wrote:

>"afan (AT) afan (DOT) net" <afan (AT) afan (DOT) netwrote on 10/18/2005 01:50:20 PM:
>

>
>>Hi to all!
>>
>>I have tables products and categories
>>
>>CREATE TABLE categories (
>cat_id INT(6) NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
>p_id INTEGER(6) UNSIGNED NULL,
>c_id INTEGER(6) UNSIGNED NULL,
>PRIMARY KEY(pc_id),
>INDEX pc_index(p_id, c_id),
>>);
>>
>>, 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 ABUT 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

Message
From: <afan (AT) afan (DOT) net>
To: <mysql (AT) lists (DOT) mysql.com>
Sent: Tuesday, 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 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) NT NULL AUTINCREMENT,
quantity INTEGER(6) NT NULL,
product_id INTEGER(8) NT NULL,
PRIMARY KEY(quantity_id),
INDEX ac_quantities_index1(quantity)
);
--
CREATE TABLE ac_prices (
price_id INT(8) NT NULL AUTINCREMENT,
price FLAT(10,2) NT NULL,
product_id INTEGER(8) NT NULL,
product_type ENUM('regular','sale') NT 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 NT 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

--
SGreen (AT) unimin (DOT) com wrote:
>
>"afan (AT) afan (DOT) net" <afan (AT) afan (DOT) netwrote on 10/18/2005 01:50:20 PM:
>
>
>
>>Hi to all!
>>
>>I have tables products and categories
>>
>>CREATE TABLE categories (
>cat_id INT(6) NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>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 NT NULL AUTINCREMENT,
>ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
>p_id INTEGER(6) UNSIGNED NULL,
>c_id INTEGER(6) UNSIGNED NULL,
>PRIMARY KEY(pc_id),
>INDEX pc_index(p_id, c_id),
>>);
>>
>>, 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 ABUT 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
>
>
>

afan,

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

First two footnotes to the excellent advice offered by Rhino & Shawn on
your categories, products & products_categories tables:

1. It will be best to type the primary & foreign keys identically
unsigned, or all not.

2. To avoid rounding errors, use DECIMAL rather than FLAT for money
columns.

Before you model extended price computations, you have to ask & answer
crucial questions:
(i) do you know in advance all the kinds of price extensions that can
come up?
(ii) do you want the price rules to be (a) in the database or (b) in the
app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
(iv) does the app need to track price history (eg so it can recreate a
price computation from six months ago)?

Suppose the answers are those that most conventionally apply: only qty
and sale will ever come up, the rules will be in the app, and you can
leave history to the backups. Then you can take a very simple,
semi-normalised approach (leaving out some details):

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_sale DECIMAL(10,2) NULL
);

which permits you to enter whatever (qty cutoffs, price, sale) combos
are desired for any desired products, and find them for any product with
a very simple query. There is a risk, though: in six months the client
may find that new price extensions are needed, and/or that she needs
history after all.

Now, add the wrinkles that other possible, but presently unidentified
price extensions (eg 'special promotions', 'coupons', &c) will be
required, and that history must be tracked. Now you need at least, again
normalising only partly

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
pricemodtype_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
begindate DATE NT NULL,
enddate DATE NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL
);

the query to retrieve all the prices for a product is more complicated
but still straightforward.

course wrinkles multiply as if conjured by a Sorcerer's Apprentice.
Perhaps we should pause here for a breath. Is this the info you need?

PB

No. It doesn't work. First, I found one error: there are two columns for
same thing in ac_products ac_products_product_id and product_id.
Second, ac_extended_prices table doesn't fit with multiple solutions

:(

afan (AT) afan (DOT) net wrote:

Thanks guys for really detailed answers.

After your emails I talked to project supervisor and found that there
is "some changes" in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(

(ii) do you want the price rules to be (a) in the database or (b) in
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next
price is NT for x% lower or for $x lower. There is no rule. If you
were thinking on something else please explain. Thanks.

(iv) does the app need to track price history (e.g. so it can recreate
a price computation from six months ago)?
- This would be actually more online catalog where visitor/customer
will create an inquiry. And we don't need to track a purchase history
in this case.

But, Peter's 2nd part is actually "touching" the change in the
project: product can have more then 2 prices. E.g. if you select shirt
with your logo embroidered - it's one price. If your logo will be
screened on the shirt - other price. And then if the shirt is on sale
- 2 more prices Total 4 different prices have to be shown on catalog.
The solution:

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
pricemodtype_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
begindate DATE NT NULL,
enddate DATE NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL
);

will be fine?

Actually, there is what I have for the moment for my DB:

categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NT NULL AUTINCREMENT,
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 ac_products (
product_id INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible
at front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
);
--
since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
ac_categories_cat_id INT(6) NT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX (ac_products_product_id),
INDEX (ac_categories_cat_id)
);

CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
INT(8) NT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
product_id INTEGER(8) UNSIGNED NULL,
pricemodtype_id INTEGER(8) UNSIGNED NULL,
qty_up_to INTEGER(8) UNSIGNED NULL,
begindate DATE NULL,
enddate DATE NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL,
PRIMARY KEY(epid),
INDEX ac_extended_prices_index_date(begindate, enddate),
INDEX ac_extended_prices_index_qty(qty_up_to),
INDEX ac_extended_prices_FKIndex1(ac_products_product_id ),
INDEX ac_extended_prices_FKIndex2()
);

CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NT NULL AUTINCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);
>
>
>
Your opinion?

Thanks for help.

-afan

afan,

><snipBut, Peter's 2nd part is actually "touching" the change in the
project:
>product can have more then 2 prices. E.g. if you select shirt with your
>logo embroidered - it's one price. If your logo will be screened on the
>shirt - other price. And then if the shirt is on sale - 2 more prices
Total
>4 different prices have to be shown on catalog.

Right, you have to treat price extension types as data, as suggested,
but already you have the previously unanticipated wrinkle that multiple
extensions can apply to one item, eg silkscreen the logo or embroider
it and/or it is on sale And you can be entirely sure that more
wrinkles are on the way.

That's where the question of rules comes in. Suppose when you arrive at
work tomorrow, you're told that the system has to learn how to price
some screened names by the letter and others by a single price for a
whole name. To avoid having to write a lot of awkward literal code for
all such wrinkles, you need a way of storing such rules in the
pricemodtypes table. A simple pricemodtype example might be name='logo',
method='screen', price_type='per letter', unit_price=whatever; another
might be 'screen+sale'; another might be name='logo', method='embroider,
on sale', price_type='whole', unit_price=whatever. And so on. You know
this detail, I don't. Here is one general approach: (i) collect all the
price mods the boss can tell you about, and turn them into the smallest
possible set of parameteristed formulas, (ii) create procemodtypes
columns for all the parameters you need, (iii) write generic code which
simply reads the rules and computes the parameterised prices, (iv) test
the result with the boss to ensure that you have all his rules right,
then (v) tell the boss that in the future, his rules have to fit into
those params or he has to pay for big app enhancements.

The alternative is to code every subtype literally in pricemodtypes.

you have heard all the specs, only you have talked with your boss,
so only you know what the details are going to be, and which if any can
be parameterised as above.

PB

afan (AT) afan (DOT) net wrote:

Thanks guys for really detailed answers.

After your emails I talked to project supervisor and found that there
is "some changes" in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(

(ii) do you want the price rules to be (a) in the database or (b) in
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next
price is NT for x% lower or for $x lower. There is no rule. If you
were thinking on something else please explain. Thanks.

(iv) does the app need to track price history (e.g. so it can recreate
a price computation from six months ago)?
- This would be actually more online catalog where visitor/customer
will create an inquiry. And we don't need to track a purchase history
in this case.

But, Peter's 2nd part is actually "touching" the change in the
project: product can have more then 2 prices. E.g. if you select shirt
with your logo embroidered - it's one price. If your logo will be
screened on the shirt - other price. And then if the shirt is on sale
- 2 more prices Total 4 different prices have to be shown on catalog.
The solution:

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
pricemodtype_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
begindate DATE NT NULL,
enddate DATE NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL
);

will be fine?

Actually, there is what I have for the moment for my DB:

categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NT NULL AUTINCREMENT,
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 ac_products (
product_id INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible
at front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
);
--
since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
ac_categories_cat_id INT(6) NT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX (ac_products_product_id),
INDEX (ac_categories_cat_id)
);

CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
INT(8) NT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
product_id INTEGER(8) UNSIGNED NULL,
pricemodtype_id INTEGER(8) UNSIGNED NULL,
qty_up_to INTEGER(8) UNSIGNED NULL,
begindate DATE NULL,
enddate DATE NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL,
PRIMARY KEY(epid),
INDEX ac_extended_prices_index_date(begindate, enddate),
INDEX ac_extended_prices_index_qty(qty_up_to),
INDEX ac_extended_prices_FKIndex1(ac_products_product_id ),
INDEX ac_extended_prices_FKIndex2()
);

CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NT NULL AUTINCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);
>
>
>
Your opinion?

Thanks for help.

-afan
>
>
>
Peter Brawley wrote:
>
>afan,
>>
>>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 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
>>
>First two footnotes to the excellent advice offered by Rhino & Shawn
>on your categories, products & products_categories tables:
>>
>1. It will be best to type the primary & foreign keys
>identically unsigned, or all not.
>>
>2. To avoid rounding errors, use DECIMAL rather than FLAT for money
>columns.
>>
>Before you model extended price computations, you have to ask &
>answer crucial questions:
>(i) do you know in advance all the kinds of price extensions that can
>come up?
>(ii) do you want the price rules to be (a) in the database or (b) in
>the app?
>(iii) if the answer to (ii) is (a), do you want the rules in stored
>procedures, or in tables which application code must parse?
>(iv) does the app need to track price history (eg so it can recreate
>a price computation from six months ago)?
>>
>Suppose the answers are those that most conventionally apply: only
>qty and sale will ever come up, the rules will be in the app, and you
>can leave history to the backups. Then you can take a very simple,
>semi-normalised approach (leaving out some details):
>>
>CREATE TABLE extended_prices (
>epid INT AUTINCREMENT PRIMARY KEY,
>product_id INT NT NULL,
>qty_up_to SMALLINT NT NULL,
>price_per DECIMAL(10,2) NT NULL,
>price_per_sale DECIMAL(10,2) NULL
>);
>>
>which permits you to enter whatever (qty cutoffs, price, sale) combos
>are desired for any desired products, and find them for any product
>with a very simple query. There is a risk, though: in six months the
>client may find that new price extensions are needed, and/or that she
>needs history after all.
>>
>Now, add the wrinkles that other possible, but presently unidentified
>price extensions (eg 'special promotions', 'coupons', &c) will be
>required, and that history must be tracked. Now you need at least,
>again normalising only partly
>>
>CREATE TABLE pricemodtypes (
>pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
>name CHAR(20)
>)
>>
>CREATE TABLE extended_prices (
>epid INT AUTINCREMENT PRIMARY KEY,
>product_id INT NT NULL,
>pricemodtype_id INT NT NULL,
>qty_up_to SMALLINT NT NULL,
>begindate DATE NT NULL,
>enddate DATE NT NULL,
>price_per DECIMAL(10,2) NT NULL,
>price_per_mod DECIMAL(10,2) NULL
>);
>>
>the query to retrieve all the prices for a product is more
>complicated but still straightforward.
>>
>course wrinkles multiply as if conjured by a Sorcerer's
>Apprentice. Perhaps we should pause here for a breath. Is this the
>info you need?
>>
>PB
>>
>>
>>
>
>

Thanks guys for really detailed answers.

After your emails I talked to project supervisor and found that there is
"some changes" in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(

(ii) do you want the price rules to be (a) in the database or (b) in the
app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next price
is NT for x% lower or for $x lower. There is no rule. If you were
thinking on something else please explain. Thanks.

(iv) does the app need to track price history (e.g. so it can recreate a
price computation from six months ago)?
- This would be actually more online catalog where visitor/customer will
create an inquiry. And we don't need to track a purchase history in this
case.

But, Peter's 2nd part is actually "touching" the change in the project:
product can have more then 2 prices. E.g. if you select shirt with your
logo embroidered - it's one price. If your logo will be screened on the
shirt - other price. And then if the shirt is on sale - 2 more prices
Total 4 different prices have to be shown on catalog.
The solution:

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
pricemodtype_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
begindate DATE NT NULL,
enddate DATE NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL
);

will be fine?

Actually, there is what I have for the moment for my DB:

categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NT NULL AUTINCREMENT,
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 ac_products (
product_id INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible at
front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
);

since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
ac_categories_cat_id INT(6) NT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX (ac_products_product_id),
INDEX (ac_categories_cat_id)
);

CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NT NULL AUTINCREMENT,
INT(8) NT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NT NULL,
product_id INTEGER(8) UNSIGNED NULL,
pricemodtype_id INTEGER(8) UNSIGNED NULL,
qty_up_to INTEGER(8) UNSIGNED NULL,
begindate DATE NULL,
enddate DATE NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL,
PRIMARY KEY(epid),
INDEX ac_extended_prices_index_date(begindate, enddate),
INDEX ac_extended_prices_index_qty(qty_up_to),
INDEX ac_extended_prices_FKIndex1(ac_products_product_id ),
INDEX ac_extended_prices_FKIndex2()
);

CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NT NULL AUTINCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);

Your opinion?

Thanks for help.
-afan

Peter Brawley wrote:

afan,
>
>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 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
>
First two footnotes to the excellent advice offered by Rhino & Shawn
on your categories, products & products_categories tables:

1. It will be best to type the primary & foreign keys identically
unsigned, or all not.

2. To avoid rounding errors, use DECIMAL rather than FLAT for money
columns.

Before you model extended price computations, you have to ask & answer
crucial questions:
(i) do you know in advance all the kinds of price extensions that can
come up?
(ii) do you want the price rules to be (a) in the database or (b) in
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
(iv) does the app need to track price history (eg so it can recreate a
price computation from six months ago)?

Suppose the answers are those that most conventionally apply: only qty
and sale will ever come up, the rules will be in the app, and you can
leave history to the backups. Then you can take a very simple,
semi-normalised approach (leaving out some details):

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_sale DECIMAL(10,2) NULL
);

which permits you to enter whatever (qty cutoffs, price, sale) combos
are desired for any desired products, and find them for any product
with a very simple query. There is a risk, though: in six months the
client may find that new price extensions are needed, and/or that she
needs history after all.

Now, add the wrinkles that other possible, but presently unidentified
price extensions (eg 'special promotions', 'coupons', &c) will be
required, and that history must be tracked. Now you need at least,
again normalising only partly

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTINCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTINCREMENT PRIMARY KEY,
product_id INT NT NULL,
pricemodtype_id INT NT NULL,
qty_up_to SMALLINT NT NULL,
begindate DATE NT NULL,
enddate DATE NT NULL,
price_per DECIMAL(10,2) NT NULL,
price_per_mod DECIMAL(10,2) NULL
);

the query to retrieve all the prices for a product is more complicated
but still straightforward.

course wrinkles multiply as if conjured by a Sorcerer's Apprentice.
Perhaps we should pause here for a breath. Is this the info you need?

PB
>
>
>