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.
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