0

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?

0

I would suggest creating three tables

  1. For invoices (InvoiceId Primary Key)
  2. For Products (ProductId Primary Key)
  3. For invoice items which hold InvoiceId (FK Invoices), ProductID (FK Products)

This way data redundancy will be minimized.

Thanks!

  • According to standard database design, Can a table(invoice items) can exist without a PK? – Aaron Jul 18 '19 at 12:27
  • Yes we can generate one primary key for Invoice items table too (like IDENTITY type column). But adding product id in invoice table may increase duplicate InvoiceId & customer details. Because one invoice may be generated against multiple products. – Rajesh Ranjan Jul 18 '19 at 12:33
  • And then final form of invoice created by joining those two tables and deriving required information for the particular invoice id using join queries? – Aaron Jul 18 '19 at 12:44
  • 2
    I would disagree with that. Price can change often. You will want to record both. The price at the time of sale on the transaction, and the current price in the products table. You may even want to maintain that price history elsewhere too. If you do not do this, you will have no idea how your transactions were calculated previously. – Jacob H Jul 18 '19 at 13:27
  • 1
    I think you're really over-complicating things here on the low-level details of the design, but lumping unit price * quantity into a single value might not be the best. What if they have buy 3 get one free, or save 10% when you buy more than 5, there are all kinds of things where total price will hide away details that are important when the customer tries to return one... – Aaron Bertrand Jul 18 '19 at 14:01
2

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.

  • And then final form of invoice created by joining those two tables and deriving required information for the particular invoice id using join queries? – Aaron Jul 18 '19 at 12:36
  • Yes. Essentially the query for the invoice would return all ItemIDs and quantities from the invoice items table, where they have the matching invoice id. That would be joined to the invoice table to gather the customer information that is attached to the invoice – John Herbert Jul 26 '19 at 15:14
2

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,
  ...
);
  • According to your InvoiceDetails table, if one customer buys more products, how to insert records in this table? because, we can't repeat the PK of InvoiceDetailID for each product in InvoiceDetails table! – Aaron Jul 18 '19 at 12:41
  • Please have look at this example @Aaron Bertrand ibb.co/YRmXzH1 – Aaron Jul 18 '19 at 12:53
  • 2
    @Aaron Why do you need InvoiceDetailID to be the same? If one customer buys multiple products under a single invoice, InvoiceID will be the same. InvoiceDetailID is there simply to uniquely identify a row, in cases where future expansion of functionality might exist (consider the case where in one order I buy two razors and have one sent to my house and one sent to my dad - I need two detail line items, one for each address). – Aaron Bertrand Jul 18 '19 at 13:00
  • So you meant, InvoiceDetailID must be added but not necessary or we shouldn't include it in final form of generating invoice by joining two tables? Please consider my example. ibb.co/BZJpGwL – Aaron Jul 18 '19 at 13:09
  • IMO both PK's could be identities. – user179264 Jul 18 '19 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.