If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Database normalisation

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,794
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 2
Thanks Pat
Which fields would go in which table? Is it just a concat PK in invoiceorders?
Thanks
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,794
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.
Reply With Quote
  #5 (permalink)  
Old
Super Moderator
 
Join Date: Jun 2004
Location: Arizona, USA
Posts: 1,839
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

Reply With Quote
  #6 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,218
but its not very likely its a real world requirement after all.....
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On