How to simplify e-commerce DB Design with multiple options per product?
I have an e-commerce MySQL database with the following tables:
**Product**
----------
productID - 1
categoryID - 1
productName - Cricket Bat
**prodOptions**
----------
optionID - 1
productID - 1
optionName - Bat Size
<PK>optionID - 开发者_开发知识库2
<FK>productID - 1
optionName - Bat Weight
**optionValues**
----------
optionValuesID - 1
optionID - 1
optionValue - Small
optionValuesID - 1
optionID - 1
optionValue - Heavy
**orders**
----------
orderID - 1
custID - 1
**orderContent**
----------
orderID - 1
orderContentID - 1
prodID - 1
optionNameID - 1,2
optionValuesId - 1,2
As you can see, each cricket bat has multiple options. How can I store all of the options in the order content table without entering two or more ID numbers in a row?
Add in a new orderOptions table, which contains a row for each option selected:
**orderOptions**
----------------
orderId - 1 optionId - 1 optionValue - 1
orderId - 1 optionId - 2 optionValue - 2
Thus orderId can appear twice (or more) in this table; PK is the combination of orderId and optionId.
精彩评论