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
