Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Database normalisation

    Hi All
    This is my first post, im seeking some help in designing an Access database which i will be using for PC Repairs and other services eg spreadsheet development. Below is the tables which i currently have.

    ORDERS - order_id, ord_price, ord_quote, ord_req_by, service_req, parts_req, ord_comments, service_status, payment_status, amount_tendered, amount_outstanding. PK is order_id

    CUSTOMER - client_id, cust_firstnane, cust_lastname, cust_ph, cust_email PK is client_id

    SUPPLIERS - supplier_id, supplier_name, supplier_ph, supplier_address. PK is supplier_id

    I would like to add the field Invoice, but it does not fit into any of the tables above. One invoice may have many orders, one customer may have may invoices, im thinking of creating a new table named INVOICE.

    Something like

    INVOICE invoice_id, order_id the problem with this is that if invoice_id is the PK it will not work as invoice_id will repeat in this table (one invoice may have may order_id's). Can i make order_id the PK? or is there a better way of designing this database?

    Thanks in advance
    Mike

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    Two tables, one for Invoices and the other for InvoceOrders.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Thanks Pat
    Which fields would go in which table? Is it just a concat PK in invoiceorders?
    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    Think of the tables a three distinct "widgets", where you collect information about things. The stuff that applies only to invoices goes into the invoices table. The stuff that applies only to orders goes into the orders table. The stuff that only applies to the association of an order with an invoice (like when it was done, who did it, the PK values from the invoice and the order, etc) go into the invoice orders table.

    I'd suggest a quick perusal of Boyce for more details.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,844
    Your orders table might also benefit by splitting it into an order header table and an order details table. There are data associated with the order as a whole (customer reference, various order dates, etc.) since a given order may (and often would) have multiple items (Virus cleaning, hardware replacment/add, etc.) you would probably want to include an order details table (child of the order header table) to store item specific info. Otherwisae, the service_reqd and parts_reqd fields would contain multiple pieces of information, which is a normalization no-no.
    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


  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    An Access database for tracking spreadsheet development?
    My head jus a'sploded.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    but its not very likely its a real world requirement after all.....
    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
  •