POS Design

[poymode]

Greetings.

I am making some Point of Sales system but I have a little problem with the database design.

You see, there is this list of products that will be added to a cart. What I need is when these items are added in the cart and then sold, it will generate some sort of receipt or purchase number with a given purchased date and at the same time, it will contain the items purchased (item name, quantity, price).

So lets say I have..
Item A, B and C in my Inventory and they have a price of 5,10 and 15 respectively.
Item A,B and C of are purchased by a customer. Clerk clicked sell and...
A Purchase Number 1 is generated which contains the three items A,B and C with there prices and quantity.

This are my ideas of doing it..
Lets say these are my entities

[B]ITEM[/B]
item_id | item_name | price | quantity
1             A         5           5
2             B         10         5
3             C         15         5

[B]ORDER[/B]
item_id | quantity_purchased
1            2
2            3

[B]PURCHASE[/B]
purchase_no | pur_date | items_purchased_for_this_purchase_no
00001          12/30/09         1-2

Would this be a good design?

OR this..

[B]ITEM[/B]
item_id | item_name | price | quantity
1             A         5           5
2             B         10         5
3             C         15         5

[B]ORDER[/B]
order_id | item_id | quantity_purchased | purchase_no
1             1                    3             0001
2             2                    2             0001
3             3                    3             0002

[B]PURCHASE[/B]
purchase_no | purchase_date | 
0001           12/30/09
0002           12/31/09

Or could anyone help me with a better design?

Thanks in advance.

[JuhaW]

When I made POS systems 10 years ago all fields were repeated in every tables: price, quantity, discounts, etc. When one line was sold, there should not change these fields in any circumstances. All correction made with new line to recipe. Order is a little bit easier, because there won't exchange any cash, still they are legal agreements.

[Lady_Dex]

Hi Poymode

Option 2 is a much better approach. How about renaming the ORDER table to be PURCHASE_LINE or PURCHASE DETAIL, something like that so it indicates that the table is the related to the purchases.

Depending on how complex the business requirements are, you may need to look at adding other fields to the purchasing tables. For example, for each item there may be a ORDERED quantity and a RECEIVED quantity. Also, on the ITEM table, there is usually a COST field and a PRICE field. Cost being related to what you pay for an item, and Price being what you sell the item for.

Hope this helps a little :)

[asaukani]

Hi Poymode

Option 2 is a much better approach. How about renaming the ORDER table to be PURCHASE_LINE or PURCHASE DETAIL, something like that so it indicates that the table is the related to the purchases.

Depending on how complex the business requirements are, you may need to look at adding other fields to the purchasing tables. For example, for each item there may be a ORDERED quantity and a RECEIVED quantity. Also, on the ITEM table, there is usually a COST field and a PRICE field. Cost being related to what you pay for an item, and Price being what you sell the item for.

Hope this helps a little :)

if u really made POS such as used in supermarket or others normally we don't keep customer order because the customer bring the items to the cashier so we can store only items puchased
when i design the retail system i don't use purchase for this but sales because actually what happened is items are sold to the customer
TO do this we keep two files:
SalesHdr contains: transaction-id, date and time of transaction, cashier-id
SalesDtl contains: dtl-id, transaction-id, item-id, quantity, price, cost
cashier-id is important to know who make the sales so end of day u can calculate how much money should be in the drawer
price and cost should be kept because it can be changed over time so we can make sales analysis better
if u make it for cart operation like e-commerce, u can add field quantity requested in SalesDtl, so we can do backorder
hope my info would be helpful

[safeer ahmad]

i wants make pos software database guaide me