List:General Discussion« Previous MessageNext Message »
From:afan@afan.net Date:October 20 2005 9:30pm
Subject:Re: one product in more categories
View as plain text  
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
>>
>>
>>
>
>
Thread
one product in more categoriesafan@afan.net18 Oct
  • Re: one product in more categoriesSGreen18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
      • Re: one product in more categoriesPeter Brawley18 Oct
        • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesPeter Brawley19 Oct
          • Re: one product in more categoriesSGreen19 Oct
            • Re: one product in more categoriesafan@afan.net20 Oct
              • Re: one product in more categoriesPeter Brawley20 Oct
                • Re: one product in more categoriesafan@afan.net20 Oct
                  • Re: one product in more categoriesPeter Brawley21 Oct
              • Re: one product in more categoriesSGreen21 Oct
                • Re: one product in more categoriesafan@afan.net21 Oct
                  • Re: one product in more categoriesSGreen21 Oct
    • Re: one product in more categoriesRhino18 Oct
  • Re: one product in more categoriesRhino18 Oct
    • case insensitive searchafan@afan.net19 Nov
      • Re: case insensitive searchJasper Bryant-Greene19 Nov
        • Re: case insensitive searchafan@afan.net20 Nov
          • Re: case insensitive searchBjörn Persson21 Nov
      • Re: case insensitive searchScott Haneda19 Nov
      • Re: case insensitive searchBjörn Persson19 Nov