If you have, and always will have, no more that 64 product types, you can
store the product type in a SET field. This is effectively a hybrid between
your list and boolean solutions. MySQL stores this as a bitmap with one bit
per product type and
provides the appropriate query methods. You can then add a second set field
for Categories.
The online manual is not particularly helpful about this type. Paul DuBois
book (for example) has much more informative
Alec.
-----------------------------------------------------------------------------------
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
---------------------------------------------------------------------
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
