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