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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.