Help to design MySQL database structure
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Help to design MySQL database structure

  1. #1
    Join Date
    Jul 2012
    Location
    Mexico, DF
    Posts
    2

    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!

  2. #2
    Join Date
    Aug 2007
    Posts
    140

    Re: Help to design MySQL database structure

    Dr. Codd would turn over in his grave if you choose #2.

  3. #3
    Join Date
    Jul 2012
    Location
    Mexico, DF
    Posts
    2

    Re: Help to design MySQL database structure

    Hi Ned, thanks for your comment. In terms of design I agree that option 2 breaks the relational rules, but my concern is in terms of the volume because the data stored is to big at the detail level and the resources are too limited for the database and the application that manages that information.

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center