-
July 18th, 2012, 02:45 PM
#1
Help to design MySQL database structure
Please, I need some help to design a database (as better as posible). I have a set of products made with diferent sizes and customers can create Orders asking for many products and variables quantities of each of their available sizes.
My doubt es which of the following schemas is better?:
1.- Create a table of Products, create a table of Product Sizes, create a table of Orders and create a table for each quantity requiered. For example see the following draft tables (only main fields are shown)
Products
---------
productCode
description
ProductSizes
--------------
productCode
productSizeDescription
Orders
-------
orderNumber
orderDate
OrderDetail
-------------
orderNumber
productCode
productSizeDescription
quantityRequired
2.- Create a table of products with multiple fields to define all available product sizes, create a table of Orders with multiple fields to store all quantities required for product size. For example see the following draft tables (only main fields are shown):
Products
---------
productCode
description
productSize1
productSize2
productSize3
...
productSizeN
Orders
-------
orderNumber
orderDate
productCode
productSize1
requiredQuantity1
productSize2
requiredQuantity2
productSize3
requiredQuantity3
...
productSizeN
requiredQuantityN
First approach seems to me the most relational, but their disadvantage is that I need many small records only for the quantities for each product size required.
The second one, is not completely relational but requires only one big record to store all the information regarding to the Order and the quantities required by one product at a time.
I think is not relevant but I plan to use MySql database.
I hope some can guide me to decide wich schema is better or maybe suggest another one.
Thanks in advance, and I apologize for may poor english!
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|