pos design prob

poymode · #1 (**permalink**) 12-30-09, 06:25

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

Code:

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

Code:

ORDER
item_id | quantity_purchased
1            2
2            3

Code:

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..

Code:

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

Code:

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

Code:

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.

andrewst · #2 (**permalink**) 12-30-09, 09:30

I'm a bit confused by your terminology. Am I right in thinking that what you call a Purchase is what I would normally call an Order, and what you call an Order is what I would call an Order Line?

I would expect a physical Order and its Order Lines to resemble:

Code:

Order No: 1234567     Date: 01/12/2009

Line  Item#  Item Desc        Unit Price  Qty   Cost
----  ------ ---------        ----------  ---  -----
1     123    Light sabre            3.99  3    11.97
2     479    Sink plunger           1.49  2     2.98
                                               -----
Order Total                                    14.95

So I would have:

Table ITEMS:
- Item No
- Description
- Unit Price

Table ORDERS:
- Order No
- Order Date

Table ORDER_LINES:
- Order No
- Line No
- Item No
- Quantity

Efectively, with a change of terminology, I think this equates to your option 2. Your option 1 has an odd denormalised attribute "items_purchased_for_this_purchase_no" that isn't wanted.

MarkATrombley · #3 (**permalink**) 12-30-09, 10:59

I agree with Tony, but the ITEMS table needs an Inventory Quantity.

The Line No in the Order_Lines table is optional. It depends if you care what order the items were added or not.

poymode · #4 (**permalink**) 12-30-09, 11:02

Quote:

Originally Posted by andrewst

I'm a bit confused by your terminology. Am I right in thinking that what you call a Purchase is what I would normally call an Order, and what you call an Order is what I would call an Order Line?

I would expect a physical Order and its Order Lines to resemble:

Code:

Order No: 1234567     Date: 01/12/2009

Line  Item#  Item Desc        Unit Price  Qty   Cost
----  ------ ---------        ----------  ---  -----
1     123    Light sabre            3.99  3    11.97
2     479    Sink plunger           1.49  2     2.98
                                               -----
Order Total                                    14.95

So I would have:

Table ITEMS:
- Item No
- Description
- Unit Price

Table ORDERS:
- Order No
- Order Date

Table ORDER_LINES:
- Order No
- Line No
- Item No
- Quantity

Efectively, with a change of terminology, I think this equates to your option 2. Your option 1 has an odd denormalised attribute "items_purchased_for_this_purchase_no" that isn't wanted.

Yes. Sorry for my wrong terminologies. This is the one I need.

So with this design, can you help me a bit in the flow to the database. This is my flow..

1. Selected Items go Into Cart.
2. SELL button is pressed. For each cart item (Item No, Quantity), generate a Line No.
3. Also, generate an Order ID with Date.
4. Insert the latest Order ID generated to each of the generated Line No.

Specifically, base on the example above.
1. Skip...
2.
so on my database.
ORDER_LINES

Code:

Line No    Item_No    Order_No   Quantity(purchased)
-------    -------     ---------   --------
    1        123        1234567         3
    2        479        1234567         2

at the same time..
ORDER

Code:

Order_No     Order_date
---------    -----------
1234567      01/12/2009

is that a good way to insert it into the database?

@MarkATrombley
hey, thanks for replying, Yeah, I have an inventory quantity in my plan, maybe andrewst didnt include it for simple purposes.

andrewst · #5 (**permalink**) 12-31-09, 11:40

Quote:

Originally Posted by poymode

is that a good way to insert it into the database?

I'm not quite following (again!). My code would be something like this pseudo-code:

Code:

IF (cart is not empty) THEN
    v_order_no := <get next order no>;
    insert into orders (order_no, order_date) values (v_order_no, <today's date>);
    FOR each item in cart
        insert into order_lines (order_no, line_no, item_no, quantity)
        values (v_order_no, <cart item line no>, <cart item item no>, <cart item quantity>);
    END FOR
END IF

... if you see what I mean?

Quote:

Originally Posted by poymode

Yeah, I have an inventory quantity in my plan, maybe andrewst didnt include it for simple purposes.

No, I just forgot to put it in!

poymode · #6 (**permalink**) 12-31-09, 13:14

Quote:

Originally Posted by andrewst

I'm not quite following (again!). My code would be something like this pseudo-code:

Code:

IF (cart is not empty) THEN
    v_order_no := <get next order no>;
    insert into orders (order_no, order_date) values (v_order_no, <today's date>);
    FOR each item in cart
        insert into order_lines (order_no, line_no, item_no, quantity)
        values (v_order_no, <cart item line no>, <cart item item no>, <cart item quantity>);
    END FOR
END IF

... if you see what I mean?

No, I just forgot to put it in!

Hey There. ONCE AGAIN, I failed to deliver what I wanted and ONCE AGAIN you sorted it out for me. Thank you very much and your pseudocode is just what I also had in mind, just that I delivered it wrong. Thanks for your help. =)

Happy new Year.

Internet Services

Web Development

Digital Marketing

Consumer Tech