Somethign like this?
insert into products values (null, 'AP1520', 'Ultra Blend 50/50 Sport
Shirt', '5.6 ounce, 50% cotton/ 50% polyester fabric. Contoured collar
and cuffs, double-stitched seams. Clean finished placket with
reinforced box and 3 woodtone buttons. Sizes M-3X.', 'Neutrals -
White(30n) Heathers - Ash (93h), Sport Grey (95h) Colors - Gold (24c),
Navy (32c), Forest Green (33c), Black (36c), Orange (37c), Red (40c),
Royal (51c), Lt Blue (69c), Purple (81c), Maroon (83c)', 'Price
includes 1-color screened imprint. Screen charge, add $24.00(g) per
color, per location. Additional imprint colors add $.42(c) per color
per location. Embroidered imprint - 1-location, up to 5 thread colors,
5000 stitches. Tape charge, add $75.00(g) for 5,000 stitches, over
5,000 stitches add $15.00(g) per 1,000 stitches. Run charge over 5,000
stitches, add $.45(c), per 1,000 stitches. Add $2.75(c) per 2X - 5X.',
'Katalog_Ime', 1, 'Vernon', 2005-10-10, 2005-12-31)
INSERT INTO methods VALUES (1, 'Regular');
INSERT INTO methods VALUES (4, 'Emboidered');
INSERT INTO methods VALUES (5, 'Screened');
INSERT INTO methods VALUES (6, 'Screened - dark');
INSERT INTO prices VALUES (null, 'AP1520', 4, 12, '9.07', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 24, '8.91', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 48, '8.83', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 72, '8.75', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 144, '7.22', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 12, '9.84', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 24, '9.66', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 48, '9.58', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 72, '9.49', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 144, '7.93', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 12, '10.42', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 24, '10.23', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 48, '10.14', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 72, '10.05', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 144, '8.47', 0);
And if I have Embroidered on sale:
UPDATE prices SET sale_price = '7.07' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.91' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.83' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.75' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '5.22' WHERE price_id = $price_id);
Does it make a sense?
-afan
Peter Brawley wrote:
afan,
The model is progressing. The test for how well it works is what
happens when you ask it to represent all presently known types of
products. What happens when you hae to apply two 'methods' to one
product?
PB
-----
[EMAIL PROTECTED] wrote:
Could you please tell me should this work?
CREATE TABLE categories (
cat_id INTEGER(8) UNSIGNED 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,
PRIMARY KEY(cat_id)
)
TYPE=InnoDB;
CREATE TABLE products (
prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
prod_no VARCHAR(12) NOT NULL,
prod_name VARCHAR(45) NOT NULL,
prod_description TEXT NULL,
prod_colors TEXT NULL, // since there are tons of colors,
defined differently by different suppliers, I had to go this way for
color options
prod_includes TEXT NULL,
prod_catalog VARCHAR(45) NULL,
prod_status ENUM('0','1') NOT NULL DEFAULT 0,
prod_supplier VARCHAR(45) NULL,
prod_start_date DATE NULL,
prod_end_date DATE NULL,
PRIMARY KEY(prod_id),
INDEX products_index1(prod_status),
INDEX products_index2(prod_start_date, prod_end_date)
)
TYPE=InnoDB;
// since one product could be in more then one category, I
created this assoc. table
CREATE TABLE categories_has_products (
categories_cat_id INTEGER(8) UNSIGNED NOT NULL,
products_prod_id INTEGER(8) UNSIGNED NOT NULL,
PRIMARY KEY(categories_cat_id, products_prod_id),
INDEX categories_has_products_FKIndex1(categories_cat_id),
INDEX categories_has_products_FKIndex2(products_prod_id),
FOREIGN KEY(categories_cat_id)
REFERENCES categories(cat_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(products_prod_id)
REFERENCES products(prod_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
// table methods represent different kind of imprints on
apparels. same an apparel with different kind of imprint could have
a different price.
CREATE TABLE methods (
met_id INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
met_name VARCHAR(12) NULL,
PRIMARY KEY(met_id)
)
TYPE=InnoDB;
CREATE TABLE prices (
price_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
products_prod_id INTEGER(8) UNSIGNED NOT NULL,
methods_met_id INTEGER(4) UNSIGNED NOT NULL,
qty INTEGER(8) UNSIGNED NULL,
price DECIMAL(10,2) NULL,
sale_price DECIMAL(10,2) NULL,
PRIMARY KEY(price_id),
INDEX prices_index1(qty),
INDEX prices_FKIndex1(methods_met_id),
INDEX prices_FKIndex2(products_prod_id),
FOREIGN KEY(methods_met_id)
REFERENCES methods(met_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(products_prod_id)
REFERENCES products(prod_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
Only thing that bothers me is start and end dates for sale prices:
shirt: available from today until 4/1/2006
price: $7.95
on sale from 11/15/2005 to 1/15/2006
sale price: $5.95
Right now, with "my" solution, administrator has to turn on/off "on
sale". I need to automate this by start and end dates for sale.
Any ideas?
Thanks for any help.
-afan
