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