From: Peter Brawley
Date: October 20 2005 10:30pm
Subject: Re: one product in more categories
List-Archive: http://lists.mysql.com/mysql/190650
Message-Id: <43581A6D.6040504@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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