Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: many products to an order?

    Hi I have a table for customers, products and orders. However I can only seem to manage one product per order, how would I manage to get more than one product within one order line without repeating data.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider another (sub) table OrderDetails
    Order holds information on the order (eg customer, delivery address, order no (PK) etc)
    OrderDetails contains details of products ordered orderNo (FK) productNo (FK) qty ordered price and tax rate or tax value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Is it fine that the product number in order details repeats?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well I guess it depends on the business practice

    I've never seen multiple rows of the same product in the same order_detail in the real world. It can cause confusion and complications in order picking and goods reveiving.

    normally I'd put a unique constraint on order + product no in the order detail. its optional as to whether you make that the primary key or create an item number and use order number + item number as the Primary Key. there is no hidebound answer I've seen both out there in the wild
    the advantage of using the product number is that its a natural key, which is to be encouraged where possible
    the advantage of the item number is that items remain in the order the user as ordered them in. so if a user is creating an order with 'stuff' in a sequence known only to them then thats how they appear inthe order (AND how they should appear ont he delivery note + invoice)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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