database design pos

[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

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

ORDER
item_id | quantity_purchased
1            2
2            3

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

Would this be a good design?

OR this..

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

ORDER
order_id | item_id | quantity_purchased | purchase_no
1             1                    3             0001
2             2                    2             0001
3             3                    3             0002

PURCHASE
purchase_no | purchase_date | 
0001           12/30/09
0002           12/31/09

Or could anyone help me with a better design?

Thanks in advance.

[MarkCuering]

wow... a huge crap tables after 5 months or over... the design is straigthforward and simple. But to be honest with you, not expandable, and cannot be scaled out, worst is may led to a very poor performance.

Are you willing to do "SELECT" over 100000 items just to modify 1 value? or create numbers of internal table and perform updates?

If you are expecting a flexible design that grows over a number of years, think ahead.

nice and helpful thread.

[uchihacirxe]

Suggestion:

For the Item Master File

========================================================
ITEMCODE (PK)     Barcode (PK)     Description     Selling Price    Inventory 
========================================================
1                 1B                   ONE B            10.00            100
2                 2A                   TWO A           15.00             50
========================================================

[uchihacirxe]

For POS tables:

I would suggest a Master-Detail Implementation

pos_hdr
{
pos_id (pk),
or_no (pk),
transaction_date_and_time,
gross_amount,
net_amount
}

pos_dtl
{
seqno autoincrement (pk),
pos_id (fk)
or_no (fk)
itemcode,
qty_sold,
price
discount,
amount
}

[uchihacirxe]

Separate the Sales Table.

sales
{
seqno autoincrement (pk),
or_no,
sales_date,
itemcode,
barcode,
qty_sold,
price
}

[MarkCuering]

isn't that expensive for a single transaction? especially if you want to modify selling price or inventory.

[uchihacirxe]

isn't that expensive for a single transaction? especially if you want to modify selling price or inventory.

If you would like to modify the selling price the only table you will be dealing with is the Item Master File.

This goes for the inventory column as well. The difference is you have the option to indirectly update the inventory column by using a trigger on the sales table. This is besides programmatically updating it.

[uchihacirxe]

I don't see how this is expensive. Can you see how flexible this is?

[ingkiang]

wow... a huge crap tables after 5 months or over... the design is straigthforward and simple. But to be honest with you, not expandable, and cannot be scaled out, worst is may led to a very poor performance.

Are you willing to do "SELECT" over 100000 items just to modify 1 value? or create numbers of internal table and perform updates?

If you are expecting a flexible design that grows over a number of years, think ahead.

nice and helpful thread.

if u have a nice idea please do share...

[uchihacirxe]

calling all DB designers. Please share your ideas.