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