List:General Discussion« Previous MessageNext Message »
From:Ken Easson Date:February 3 2003 9:55pm
Subject:best way to optimize a table with many to few relationship.
View as plain text  

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

At 05:08 AM 2/3/2003 -0800, you wrote:
>From manual:
>A BLOB or TEXT column with a maximum length of
>4294967295 (2^32 - 1) characters
>If I insert a 400k file into a longblob, will it
>occupy 400k in actual space on the harddrive, or will
>it occupy the maximum for a longblob?
>Is there any difference here between innoDB an MyISAM?
>I read that MyISAM only can handle blobs up to 16
>Any thoughts?
>// Michelle
>SQL, Query
>Do you Yahoo!?
>Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
>Before posting, please check:
>   (the manual)
>           (the list archive)
>To request this thread, e-mail <mysql-thread131526@stripped>
>To unsubscribe, e-mail <>
>Trouble unsubscribing? Try:

ken easson
justken web programming and technical support. 

blob sizeMichelle de Beer3 Feb
  • best way to optimize a table with many to few relationship.Ken Easson3 Feb