What you're talking about is a many-many relationship, as one product can
belong to many categories, and one category can encompass many products.
The best way to implement this is to have a [category] table, a [product]
table, and a [categoryproduct] table. The latter table implements the
many-many relationship.
create table categoryproduct (
categoryid int,
productid int,
unique key categoryproduct (categoryid, productid)
key productcategory (productid, categoryid)
)
There's two indecies on the table to allow both forward and reverse lookups
when joining tables--find the categories a product belongs to, or find the
products for a particular category.
On a related note...always design your database to handle all
scenarios...designing for 99% will bite you in the arse later when the 1%
becomes 1,000 products :)
Best regards,
Peter
<^_^>
> -----Original Message-----
> From: Ken Easson [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 03, 2003 4:55 PM
> To: [EMAIL PROTECTED]
> 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
>
> At 05:08 AM 2/3/2003 -0800, you wrote:
> >From manual:
> >LONGBLOB
> >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
> >mb...
> >
> >Any thoughts?
> >// Michelle
> >SQL, Query
> >
> >__________________________________________________
> >Do you Yahoo!?
> >Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> >http://mailplus.yahoo.com
> >
> >---------------------------------------------------------------------
> >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
>
> 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
