List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 21 2005 2:42pm
Subject:Re: one product in more categories
View as plain text  
Response intermixed: 

"afan@stripped" <afan@stripped> wrote on 10/21/2005 09:19:41 AM:

> Thanks Shawn.
> See  comments:
> 
> SGreen@stripped wrote:
> 
> >Sorry I didn't get back to you earlier today. I have had a busy day. 
> >Comments embedded...
> >"afan@stripped" <afan@stripped> wrote on 10/20/2005 04:35:30 PM:
> >
<snip>
> >>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?

There are rules for colors. Your suppliers give you a list of all of the 
combinations of colors for each product, right? That's a rule: "Product Y 
comes in Midnight Blue, Aqua, and Cobalt". Any other color would be wrong, 
correct? 

Whenever you start making "lists" in text fields, you have the opportunity 
to normalize those lists into their own tables. Sure, it adds a layer of 
design but it's a more flexible design as you can create a product-color 
association table that contains start dates and end dates for special 
color runs or that contains a pointer to your inventory tables so that 
when a particular color runs out of stock, you can stop listing it on your 
site. As a rough example:

CREATE TABLE color_product {
        color_id,
        product_id,
        inventory_sku,
        startdate,
        enddate, 
);

Every color value (including all unique color combinations) for any 
product are listed once (in their own table), product are listed once (in 
their own table) and there is an entry in color_product for each 
combination of product to color. The startdate and enddate columns could 
be used for automatically expiring listings like black/orange around 
Halloween or red/green/white around Christmas. That way when the holiday 
season is over, you can stop listing it automatically based simply on the 
current date (no manual deletions) .


> >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?

It's useful information and because you probably use it in EVERY query, 
adding it as a secondary column to each of your other indexes is probably 
a good idea.

> Could you please give me more info on "Consider using it as part of a 
> compound index instead"?
> 

A compound index is simply an index that uses more than one column. If you 
find that you are always making queries like

SELECT ....
FROM ...
WHERE category_id = xxx AND status=1

Then creating a compound index on (category_id, status) makes that query 
(any any other query that uses those combinations of fields) more 
responsive because the index is more selective (better cardinality). As an 
added bonus it would also acts as a simple index for the column 
category_id. And, another bonus, if you are looking for all numeric data 
and all of the data you want actually takes part in an index, the 
optimizer will skip reading the actual table file and return the data for 
the query from the index file. This is called a "covering index" for your 
query.  The restriction on all numeric data may be lifted with recent 
developments, I haven't keep up with that aspect fo the design changes 
(sorry!).

To summarize: If you define a 3-column key that looks like (A, B, C) that 
definition performs as an index for the following combinations of terms: A 
(by itself), A and B at the same time, A and B and C at the same time. Any 
index (PRIMARY KEY, KEY, or UNIQUE) provides this functionality. That is 
why I keep trying to point out that certain KEYs (indexes) that you define 
are redundant because you, for example, listed a column first in a 
compound PK then created a separate simple key on just that column. The PK 
will do both jobs. No need to use the space to re-index that column by 
itself. 

<snip>
> 
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> > 
> >
> Thanks Shawn!
> 
> -afan
> 
> 

Any time!

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