Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: Database design/relationships

    I am currently designing a small database, it requires:

    A purchase order has a product order number(PK)

    A puchase order can have multiple items (need to store price, quantity, description and have an id)

    Each purchase order has a supplier (who supplies the products)

    A order has a supplier (supplies the items), which has a contact team member (a supplier can have multiple contact team members but will only assign 1 to an order).

    Here are the tables I have so far:

    Purchase order
    PurchaseOrderNumber (PK)
    date
    payment method
    supplierid (FK)
    supplier contact id (FK)

    ItemOrder
    Itemno (PK)
    PurchaseOrderNumber(FK)
    ITemQuantity
    ItemDesc
    ItemUnitCost

    TblSupllier
    SupplierID (PK)
    SupplierName
    SupplierDiscount

    TblSupplierContact
    SuppliercontactID (PK)
    SupplierId (FK)
    SuppliercontactFirstname
    SuppliercontactSurname


    Do any other tables need adding? One problem I think I might have is when adding an item,
    it will create a new purchase order number, when I want to be able to have multiple items in one order.

    Any help is much appriciated.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You need an item table, which will hold item-specific data. The item/order table then just holds the ID of the items that it's ordering.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2012
    Posts
    21
    Thanks for the reply, you mean like this:

    Gliffy Public Diagram - PurchaseOrder

    Thanks

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yep, like that!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Sep 2012
    Posts
    21
    Thanks, this is how I originally did it, but someone in the office suggested the other way and I came into problems! My main problem was when entering the data into the form, I could only have 1 item to one order, when more than often I would need to insert multiple items into one order.

    So now, I will need to create a query based on the tabels, and then base the form on the query?

    Can you see any other problems/suggesions with the Entity Relationship Diagram?

    Thanks again for the help

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    work through your tables and make certain the design meets the requirements

    if it helps create the tables and populate with data to make certyain the needs are met
    instead of creating the tables another technique is to use old fashioned (but still useful) pencil and paper
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2012
    Posts
    21
    Looking back at the ERD, I will not need a client table, as this will be irrelavent, and will not be displayed on the invoice (the main purpose of the system) - so this will be removed.

    I also have been informed we will not longer need to record a delivery address, so was thinking of including the payment method and orginator name into the purchase order table.

    Finally, dose the Supplier table need to be linked to purchase order? Or is it ok as it is?

    Thank you

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you are confident of the indirect link from supplier to PO (via the contact), this is fine as it is. You can always query back the name of the supplier from the contact.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Sep 2012
    Posts
    21
    Quote Originally Posted by healdem View Post
    work through your tables and make certain the design meets the requirements

    if it helps create the tables and populate with data to make certyain the needs are met
    instead of creating the tables another technique is to use old fashioned (but still useful) pencil and paper
    Thanks, I'll go through them again, I initially drew about 5 out! It does help alot.

  10. #10
    Join Date
    Sep 2012
    Posts
    21
    Sorry, one last thing which has been puzzeling me from the start.

    If I do create a new table, which stores the purchase order number and item number, how would this be used in the form (so that the purchase number and item number were generated automatically when entering data)

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You can set the PK fields to Autonumber, which means that every time someone starts to enter a record, a new number will be generated. If the record is abandoned, the number is abandoned too and skipped the next time around.

    If you need an unbroken sequence, you'll need to write or find a custom-built function to generate a number each time a record is created.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Sep 2012
    Posts
    21
    Hi, I dont think I explained my last problem in enough detail, but I think I can hopefully figure it out myself.

    Thanks for the help guys!

Posting Permissions

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