Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Orders table design question

    Hi there, I'm trying to create a simple shop database, which can hold the following info...
    • Products
    • Customer details
    • Orders


    I can't seem to get my head around how I can store more than one product_id (and its quantity) in a orders record without using another table.

    So I'd have a orders table, with a order_id for each record, which would be linked to a orderdetails table. Each record in the orderdetails table would hold the order_id it was for, the product_id and quantity ordered.

    I hope this makes sense to someone as I'm a bit confused now .

    Any advice would be great - thanks.

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    Yes, you would need an order detail table that has a M:1 relationship with order.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And, since you could order more than one product in a given order, you should also add a line number field in the order details table.

    The PK for the order details would then be the order ID AND the Line Number.

    The order ID would be a FK to the orders table...the part number would be a FK to the parts table.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jul 2003
    Posts
    74

    Here's a relevant Data Model

    [QUOTE=subfocus]Hi there, I'm trying to create a simple shop database, which can hold the following info...

    This Data Model might help to clarify your thinking :-
    http://www.databaseanswers.org/data_...ders/index.htm

    The same site lists many Customer-related Data Models :-
    http://www.databaseanswers.org/data_models/index.htm

    HTH

    B.Dimple
    Senior DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •