1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Question for Database Gurus

Discussion in 'Computing and ICT' started by Training_2_Teach, Jun 9, 2011.

  1. Training_2_Teach

    Training_2_Teach New commenter

    Ok, I have a relational database with four tables:

    Table 1: Suppliers (Primary Key Supplier_ID)
    Table 2: Departments (Primary Key Department_ID)
    Table 3: Orders (Primary Key Order_ID)
    Table 4: Items (Primary Key Item_ID)

    Table 3 Orders contains foreign keys linking to Departments and Items:

    Order_ID (Primary Key )
    Department_ID (Foreign Key)
    Item_ID (Foreign Key)

    My question: I want to allow departments to place orders for more than 1 items under one order ID as opposed to a new order ID being generated for every item ordered. How do I do this?

    Am I making sense?
     
  2. DEmsley

    DEmsley New commenter

    Perfect sense. You need an OrderLine Table.
    OrderLine - primary key is composite of Order_ID and LineNo
    • Order_ID (foreign key)
    • LineNo (Auto-Incremented)
    • Item_ID (foreign key)
    • Quantity (usually)
    Hope that makes sense.
     
  3. djphillips1408

    djphillips1408 New commenter

    Why do you need a line number field a compound PK of Order_ID and Item_ID would be fine along with the quantity.
     
  4. DEmsley

    DEmsley New commenter

Share This Page