Thanks Shawn.
See comments:
[EMAIL PROTECTED] wrote:
Sorry I didn't get back to you earlier today. I have had a busy day.
Comments embedded...
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/20/2005 04:35:30 PM:
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;
Good. I would add another INDEX for (cat_parent, cat_id) to speed up
subcategory listings.
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;
Good again.
if you don't want your application to parse out a list of colors from the
colors field, you will need a table of just colors and another association
table between colors and products.
This is why I made color columns this way.
Product 07V0128: 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)
Product 07V0205: Neutrals: **White, Natural. Lights: *Ash, Graphite,
**Light Steel. Darks - **Black, Bluestone, Cardinal, Copper, Daffodil
Yellow, Denim Blue, Gold, Gold Nugget, Kelly Green, Light Blue, Lime,
Moss, *Navy, Orange, Pink, Pebble, Pine, Purple, *Red
Product 07V0560: Apricot, Burnt Orange, Butter, Cedar, Celery, Chalky
Blue, Chalky Mint, Chalky Purple, Charcoal, Cigar, Columbia Blue,
Crimson, Dandelion, Denim, Dijon, Dorm Green, Forest, Grasshopper, Hot
Red, Indigo, Kelly, Key Lime, Latte, Lime, Mustard, Nantucke
Since, there is NO RULE for colors, this is a only solution, right?
Remember that the optimizer won't use an index if it thinks that the index
will return over 30% or so of the records in the table. With only two
values in it, an index on prod_status (all by itself) will probably never
have enough cardinality to be useful. Consider using it as part of a
compound index instead.
Looks like I'm doing wrong for a loooong time :)
Ok, then. Do I have to Index prod_status or it's not necessary?
Could you please give me more info on "Consider using it as part of a
compound index instead"?
// 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;
OK, your primary key acts as an index for categories_cat_id and for
(categories_cat_id, products_prod_id). The second index on just
categories_cat_id is redundant and can be deleted with no side-effects.
// 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;
So far, you are doing a good job at normalizing.
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
Since each price (a product-method-quantity combination) can have several
SALE prices (each with different date ranges) you need to normalize them
to their own table.
CREATE TABLE sales_prices (
sale_id int auto_increment,
price_id int not null,
saleprice,
startdate,
enddate,
PK,
FK,
FK
)
That way each Price can have a corresponding sale price for a particular
range of dates. When that range expires, the sale price no longer matches
your query and you stop showing it on the site. That's how I would solve
that particular issue.
:) I did it that way and then deleted the table because it looked to me
like two same tables (prices and sale_prices) doing same thing...
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thanks Shawn!
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
