|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
db schema question
I'm building a product catalog database with MySQL and I'm wondering if there is a more efficent way to create my tables.
Here's the structure: product ---------- product_id product_name product_desc size ----- size_id size color ----- color_id color price ----- price_id price quantity ----- quantity_id quantity map_price ------------- map_price_id product_id price_id quantity_id map_size ------------ map_size_id product_id size_id map_color --------------- map_color_id product_id color_id There are obviously more tables and more information in the db, but this all that is required to answer my question. So the relationship is, one product can have many sizes and colors, it is possible for a product to have zero sizes and colors. Size and color are not related. One product can also have up to 5 prices and quantities, price and quantity are related so that a quantity of 100 is $4.00 while a quantity of 500 is $3.80. However, this does not hold true for every product. For example, another product with a quantity of 500 might be $6.00. In this application, quantity is based on price, the users are buying the products in bulk, so for every x increase in quantity, the price will go down. Unfortunately there is no pattern between the price / quantity relationship, so I can't apply an equation to figure the pricing. I have set up mapping tables that relate the color, size, price and quantity to one product. My question is: is there a way that is just as efficent to minimize the number of mapping tables? Since size and color are not related, I can't think of a way to combine those tables. Perhaps not in this schema, but definitely in others I end up with about 5-6 mapping tables and 5-6 two-field tables like the color and size table above. This seems like a flawed design, although the data is normalized, I end up with a lot of tables.
__________________
-Samantha Last edited by Cira9999 : September 18th, 2003 at 07:12 PM. |
![]() |
| Viewing: Dev Shed Forums > Databases > Database Management > db schema question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|