CODEWITHSUNDEEP
c#sqlinvoiceinventory-management
Dev527
Dev527

Reputation: 47

how to store invoices with different number of products in it, in a single table using SQL

I am creating an Inventory management system using C# in Visual studio and the database is SQL. The thing I want is that, how can I store Invoices which I generated while billing, as these bills have different numbers of products. The question is how can I store these bills' details in a single table with invoice number as a primary key. Anybody can help me with this?

Upvotes: 0

Views: 1042

Answers (3)

Harry
Harry

Reputation: 177

Ideally, you should work on data modeling techniques used to store this kind of data. In your case, I would suggest creating two different table

  1. Invoice table (or Order table): The main table that will store information like Invoice date, Invoice ID, CustomerID, Purchase Amount, etc
  2. Invoice Details table ( or order items table): Store the order items for the Invoice ID, Product ID ( Product that was purchased, you can create another table for products as well if you require ), Quantity, price, etc.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you want to store invoices in one table, then you would probably represent the repeated data as JSON data structures.

I do not recommend that approach. You probably want two tables (at least):

  • invoices
  • invoiceLines

The first would have an invoiceId and one row per invoice. The second would have one row per "product" (or whatever) on the invoice. It would have a foreign key relationship to invoiceId.

Upvotes: 3

Neil W
Neil W

Reputation: 9112

If you really want to store the Invoice and InvoiceLines (Products) in a single table then you need a column to store serialized version of the list of products.

The following link will help:

https://www.codeproject.com/Articles/1166099/Entity-Framework-Storing-complex-properties-as-JSO

Upvotes: 1

Related Questions