I'd recommend a column of type SET.
Read about it here: http://www.mysql.com/doc/en/SET.html
Basically, if your list of product types is static, this may be a good bet. The SET
type can store any combination of 64 members,
stored as bits.
To list all items int a product type, you might use something similar to
SELECT * FROM products WHERE FIND_IN_SET('$mytype', type)>0;
I doubt that an index would be used at all -- but if you're reading 99% of the records
anyway, that's irrelevant.
Nick
----- Original Message -----
From: "Ken Easson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 03, 2003 10:07 PM
Subject: best way to optimize a table with many to few relationship.
> hello,
>
> I have a question about database structure and optimization.
>
> I have a shopping cart where several items can fall into several of many categories,
>but each item can also be several of a few
product types.
> my product type is limited to aprox. 5 product types. Further 99% of items will be
>in all 5 product types.
>
> I am trying to figure out the best way to optimize the relation ship between product
>type and product.
>
> if i create a product type table and then list each product in this type, i will
>have close to 5 times the number of rows as i
do number of products. since almost all products will be in all product type lists.
Conversely i can do an "exceptions table"
where a table lists the product types that a product is NOT in. This of course add's a
degree of difficulty if a new product type
is added.
>
> the other way of solving my problem is to add a "types" column to my product table,
>and adding the product type as a list,
however this seems strangely NOT relational database savy:
> SELECT * FROM products WHERE type IN ($mytype);
>
> OR since i have such a small number of product types, does it make sense to create a
>table which contains each product id, with
a column for each product type with a boolean field type? If this is recommended, how
is this table optimized?
>
> here is a sample of my data to illustrate where i'm coming from:
>
> item: clown
> categories: humorous, figure,
> product: pad, case, appliance
>
> item: robot-boy
> categories: si-fi, figure, abstract, robots, boys
> product: pad, case, appliance
>
> item: robot-girl
> categories: si-fi, figure, abstract, robots, girls
> product: pad, case, appliance
>
> item: dragonship
> categories: si-fi, animals, fantasy, space
> product: pad, case
>
> SELECT QUERY MYSQL
>
> ken easson
> justken.net
> [EMAIL PROTECTED]
> justken web programming and technical support.
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
