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
:(
[EMAIL PROTECTED] 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 NOT 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 AUTO_INCREMENT PRIMARY KEY,
name CHAR(20)
)
CREATE TABLE extended_prices (
epid INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
pricemodtype_id INT NOT NULL,
qty_up_to SMALLINT NOT NULL,
begindate DATE NOT NULL,
enddate DATE NOT NULL,
price_per DECIMAL(10,2) NOT 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) 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 ac_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, // 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 NOT NULL,
ac_categories_cat_id INT(6) NOT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
);
CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NOT 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) NOT 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(ac_pricemodtypes_pricemodtype_id)
);
CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);
Your opinion?
Thanks for help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
