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.
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.
>
>
> // 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.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
