I'm confused how to create tables for invoicing system, because when one invoice have more products, I can't repeat same invoice id for the each product in the invoice table as PK cannot repeat. so what is the concept followed to create tables in such scenario?
The route I have taken is to use a table for invoices, which cover the transaction data (customer, salesperson, date) and then a table for invoice items (itemID, quantity) which would have a foreign key to the invoice table. This would let you have as many items being attached to an invoice, and each invoice would still only have one record.
You would typically split this out into two tables. There is one master row per invoice, then a second table that contains all the child rows for the various products that make up the invoice. A simple start:
CREATE TABLE dbo.Invoices ( InvoiceID int PRIMARY KEY, CustomerID int, -- FOREIGN KEY REFERENCES dbo.Customers... InvoiceDate date, ... ); CREATE TABLE dbo.InvoiceDetails ( InvoiceID int NOT NULL FOREIGN KEY REFERENCES dbo.Invoices(InvoiceID), ProductID int, -- FOREIGN KEY REFERENCES dbo.Products... Quantity int, ... );