How to save purchased items in a database of a POS

Question

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 6-Sep-16 18:00pm

Chiranthaka Sampath

Updated 7-Sep-16 2:28am

Add a Solution

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?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sahan2u · Answer 1 · 2016-09-06T19:55:00

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

cormaclee · Answer 2 · 2016-09-07T02:28:00

Solution 2

Oh, thank you Sahan2u! your recommendation helped me a lot too, because I searched how to solve this case. great!

Posted 7-Sep-16 2:28am

cormaclee

Comments

Sahan2u 7-Sep-16 22:42pm

You welcome cormaclee !