2 invoice with share same client n product info, how? (1 Viewer)

klheeklhee

New member
Local time
Today, 19:22
Joined
Oct 4, 2016
Messages
2
Hi, I am HEE. How can I make this? 2 invoice (issue by 2 different company), share the SAME clients & products info.
Client's table included name, phone number, address.
Product's table included name of the product & price.
How to create the relationship in between client, product, invoice?
What tables do I need to create to built the relationship?
Thanks in advance
 

plog

Banishment Pending
Local time
Today, 06:22
Joined
May 11, 2011
Messages
11,613
You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). Specifically, associative tables (https://en.wikipedia.org/wiki/Associative_entity).

What you have is the need to create many to many relationships (many invoices can have many products). For that you create an associative table to lie in between the two tables you already have that will sort out which products go to which invoices.

You will need an Invoice table, which you didn't provide the details of, so I'll use the most likely fields:

Invoices
Invoice_ID, autonumber, primary key
Invoice_Number, text, the number external parts will use to reference this invoice
ID_Company, number, foreign key to Companies table to determine which company issued this invoice
Invoice_Date, date, date invoice was issued
... (more invoice fields here)...

Your associative table will look like this:

InvoiceProducts
IP_ID, autonumber, primary key of table
ID_Invoice, number, foreign key to Invoices.Invoice_ID to know which invoice this is for
ID_Products, number, foreign key to Products.Product_ID to know which product this is for
IP_Quantity, number, total number of this product for this invoice

That table above will establish the relationship between products and invoices. The real key is the use of autonumbers as primary keys in all your tables. Read up on those and all terms you are unfamiliar with that I have used.
 

Users who are viewing this thread

Top Bottom