Click here to Skip to main content
15,936,903 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
In a Point Of Sales System how we should arrange the database table to save the data for storing information about individual purchases. As an example a customer will purchase 1 kg of flour for USD 1.10, 2 liters of vegetable oil for USD 2.25, 2 loafs of breads for USD 1.05 with the total of USD 4.40.

The invoice no will be like 20160901-001002. So all the purchases must be grouped according to the invoice no and save in the database table. So what fields should be there in the database table? I am using MS SQL.

What I have tried:

I have tried adding tables with many to may relationships but cannot find a real solution.
Posted
Updated 7-Sep-16 2:28am
Comments
Mehdi Gholam 7-Sep-16 0:36am    
Read about master-detail relationships in databases.
Chiranthaka Sampath 7-Sep-16 1:02am    
Could you propose a database structure for this?

You should keep this invoice in two tables. One for header details and one for line level details.

Eg. In the header table you should keep data like
InvoiceNo
CustomerID (this should be a foreign key of customer table)
Date
TotalAmount...etc.


In the line level table you should keep data like
InvoiceNo (foreign key of header table)
ItemCode (foreign key of Item table)
UnitPrice
SoldQty...etc.


Then you can query as follow to get individual purchases. (I am writing this assuming you have separate tables for Item and the Customer.)

SQL
SELECT 
    H.InvoiceNo
    ,H.Date as InvoiceDate
    ,C.ID as CustomerCode
    ,C.Name as Customer
    ,I.ID as ItemCode
    ,L.SoldQty
    ,L.UnitPrice * L.SoldQty as TotalForItem
FROM InvoiceHeader H
    INNER JOIN InvoiceLine L ON H.InvoiceNo = L.InvoiceNo
    INNER JOIN Customer C ON H.CustomerID = C.ID
    INNER JOIN Item I ON L.ItemCode = I.ID
 
Share this answer
 
Oh, thank you Sahan2u! your recommendation helped me a lot too, because I searched how to solve this case. great!
 
Share this answer
 
Comments
Sahan2u 7-Sep-16 22:42pm    
You welcome cormaclee !

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900