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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
