Yep, now you try to build a frontend around it, then the users will tell
you what's wrong with it ("the purpose of the first solution is to
understand the problem").
PB
-----
afan@stripped wrote:
> 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
>>>
>>>
>>>
>>
>>
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/145 - Release Date: 10/20/2005
