Results 1 to 6 of 6

Thread: pos design prob

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    pos design prob

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  4. #4
    Join Date
    Dec 2009
    Posts
    3
    Quote Originally Posted by andrewst View Post
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by poymode View Post
    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 View Post
    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!

  6. #6
    Join Date
    Dec 2009
    Posts
    3
    Quote Originally Posted by andrewst View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •