Page 1 of 3 123 LastLast
Results 1 to 10 of 22
  1. #1

    Default database design pos


    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
    Elite Member
    Join Date
    Aug 2008
    Posts
    1,053
    Blog Entries
    1
    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.

  3. #3
    Suggestion:

    For the Item Master File
    Code:
    ========================================================
    ITEMCODE (PK)     Barcode (PK)     Description     Selling Price    Inventory 
    ========================================================
    1                 1B                   ONE B            10.00            100
    2                 2A                   TWO A           15.00             50
    ========================================================

  4. #4
    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
    }

  5. #5
    Separate the Sales Table.

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

  6. #6
    Elite Member
    Join Date
    Aug 2008
    Posts
    1,053
    Blog Entries
    1
    isn't that expensive for a single transaction? especially if you want to modify selling price or inventory.

  7. #7
    Quote Originally Posted by MarkCuering View Post
    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.

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

  9. #9
    Quote Originally Posted by MarkCuering View Post
    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...

  10. #10
    calling all DB designers. Please share your ideas.

  11.    Advertisement

Page 1 of 3 123 LastLast

Similar Threads

 
  1. Web Design Contest
    By bloodlust666 in forum Websites & Multimedia
    Replies: 8
    Last Post: 05-29-2013, 09:58 PM
  2. Replies: 7
    Last Post: 04-30-2013, 03:11 AM
  3. Replies: 0
    Last Post: 09-09-2009, 11:22 AM
  4. Template / Theme design contest inputs...
    By BeoR in forum Websites & Multimedia
    Replies: 107
    Last Post: 07-28-2006, 01:30 PM
  5. iSTORYA.NET Template Design Contest
    By BeoR in forum Websites & Multimedia
    Replies: 0
    Last Post: 09-11-2003, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
about us
We are the first Cebu Online Media.

iSTORYA.NET is Cebu's Biggest, Southern Philippines' Most Active, and the Philippines' Strongest Online Community!
follow us
#top