locked
Table Design of Purchase Order RRS feed

  • Question

  • I am now designing a PO module for my inventory system, and I am thinking "What is the Min. Number of Tables" in order to have the following features:

    1. With PO # and Created Date
    2. Able to display "Sum of the PO Amount" before Save to database
    3. Able to trace end-users' individual input (like a log)
    4. Able to Sutract the items whenever "new stock" coming in to warehouse.

    Included Tables:
    -------------------------------------------------------------------------------
    Table 1:  po_head (po_id, created_date, created_by)

    Table 2: po_tail (po_id, item_id, amount, qty)

    Table 3: po_total_qty (item_id, qty, status)

    Table 1 and Table 2 are just a Parent-Child Relationship and Table 3's data is from Table 2, whenever the new PO created.  Table 3 is used for "new stock" coming in to warehouse and do the subtraction until the status from PENDING to COMPLETE

    I explained this concept to my end-users, and some of them are still NOT happy about this design, because they cannot do tracing of "To Whom created the corresponding stock in Table 3?" (this is because Table 3's qty is a TOTAL QTY, if I don't have Table 3, how can I do the Stock Subtraction in an easy way)

    I am facing this problems (in bold face)

    Please help ~~


    • Edited by iHandler Monday, April 9, 2012 8:47 AM
    Monday, April 9, 2012 8:47 AM

Answers

  • Hi Handler,

    Sorry for late reply...Please correct me if i mis somthing ...

    for stock subtraction you have to pass a transaction in stockDetail entry (Balance entry)...QtyIn(qty under particuler enteity)..and QTYout (-tive/+tive ) based on your transaction type.. 


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    • Marked as answer by iHandler Thursday, April 12, 2012 4:11 AM
    Wednesday, April 11, 2012 11:46 AM

All replies

  • How do you perform subtraction? If you add status column to the Table 2, you still will be able to aggregate amounts and track statuses?

    If mew stock is coming in you still have to insert into Table 2 all info, right?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 9, 2012 9:00 AM
  • thanks, Let me give you further information

    Your Question 1 "How do you perform subtraction?" 
    My Ans: I'll use a table called "stock_transaction" to do the subtraction (e.g. the barcode gun "read" and stock the items and store to this table), this table looks like this  "trans_id, trans_date, trans_qty, trans_status, item_id"

    Your Question 2 "If you add status column to the Table 2, you still will be able to aggregate amounts and track statuses?"
    My Ans: not really, since Table 3 acts like a "Pool", it sums the number of qty and "waiting" for subtraction, so end-users knows how many stocks they are waiting for (or "pending") and there is NO need to do the counting manually.

    Your Question 3 "If new stock is coming in you still have to insert into Table 2 all info, right?"
    My Ans: no, Table 2 is entered by end-users, let say they need to order some items from their suppliers, and they need to create a new PO, and they need to fill out the items they want, and Table 2 used to store the item and qty...etc


    well, I think u raised our a good question (the first one), hope my ans. can give you more info.

    Monday, April 9, 2012 9:27 AM
  • You can continue with the existing design, but following enhancements may help:

    1. Rename the Qty column in Table 3 to TotalQty. This will eliminate any confusion with the Qty column in Table 2.
    2. You should have a Stock_History table with a User_ID and Transaction_Date columns. I assume a user logs in to either enter new stock or create an invoice. This will help you keep track of the person who did the transaction and on which date.
    3. You should create a trigger to add new stock of particular item and subtract on invoice from Table 3. As soon as new stock is added to Stock_History, the trigger will automatically update the Total_Qty in Table 3. So you need an AFTER INSERT/UPDATE trigger.
    Monday, April 9, 2012 6:42 PM
  • So, first off, this is the very wrong thing to think: "What is the Min. Number of Tables".  Think "What is the RIGHT Number of tables".

    Second, I am thinking that you split your thinking into Purchase Order, and Shipment/Fullfillment.  Each of thes are different states that actions that probably merit their own table (and I don't know what kind of customers you are dealing with, but Invoice should probably be considered as well, with one invoice per one or more shipments)

    So you have PurchaseOrder (PurchaseOrderId,  ToAddress/Department, Status, ...) PurchaseOrderItem(PurchaseOrderId, ItemId, Quantity, Price <and/or> Amount, Status).  Status defaults to open on both, and could be set to fullfilled or cancelled, backordered, as the case is.  A process would probably be written to create shipment rows, based on order of precedence (customer value, etc) that would take a part out of active stock and allocated it to a shipment.

    Then have a table for Shipment or Fullfullment (ShipmentId, PurchaseOrderId, FullfilledBy, Carrier, etc) ShipmentItem (ShipmentId, itemId, quantity)

    Total quantity and such could either be calculated as needed, or put in the purchas order item if necessary..  I would try to avoid triggers for process oriented values, because at any time in the process you could become unable to ship an item for some reason, and you would need to either sub a different item..

    Of course, i could be entirely missing the point, but I try to do my best to seperate every individual action into its own table, and generally have maybe one column in a table that is a status that gets modified as the actions occur that might change the status.


    Louis

    Monday, April 9, 2012 8:01 PM
  • thanks IRohitable and Louis ~~

    I revised the table design, and please take a look

    purchase_order
    - po_id, item_id, amount, qty, supplier_id, created_on, created_by, status

    purchase_order_total
    - item_id, total_qty, supplier_id, status

    assume that I already have a "stock history" table.

    Tuesday, April 10, 2012 4:27 AM
  • I am afraid I have not understood your Purchase Order concept. You cannot reduce the quantity until you finally raised an invoice against a PO.

    Anyway, I hope a single PO contains multiple items? If this is the case, your design of first table is not appropriate. I would do as below:

    Table: Purchase_Order

    po_id (P.K), created_on, created_by, status

    Table: Purchase_Order_Items

    po_id (F.K), item_id, supplier_id

    Table: Stock_History

    item_id (F.K), available_qty


    Tuesday, April 10, 2012 5:40 AM
  • Hi Handler,

    You need total 4- Tables to cover your scenario..only

    POHeader(Major Fields)- (POnumber,podate,supplierID,createdby,creteadon,modifiedby,modifiedOn,POTotal,POstatus..etc)

    PODetail(Major Fields) - (POnumber,linenumber,Item,qty,price,createdby,creteadon,modifiedby,modifiedOn,linestatus..etc)

    StockSummary(Major fields) -(id,locationID/Depot(if exists),totalstock,totalout)

    StockDetail(Your Transaction table)- (stocksummaryID,transactionlinno,docnumber/po#,transactiondate,qtyIN,Qtyout,systemdate..etc)

    after this everything is your programming logic to handle it.

    Put creteadon,modifiedOn defaullt as current date in table design.

    Hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Tuesday, April 10, 2012 8:56 AM
  • thanks Rohit Khare.

    You mentioned that "You cannot reduce the quantity until you finally raised an invoice against a PO"  Do you mean I need to have a Sales Order module in order to reduce the quantity of the PO?  If yes, do I need to have TWO modules (Purchase Order and Sales Order) at the same time in order to make the system work in a logical way?

    Please advice


    • Edited by iHandler Tuesday, April 10, 2012 8:57 AM
    Tuesday, April 10, 2012 8:56 AM
  • Hi Handler,

    You need total 4- Tables to cover your scenario..only

    POHeader(Major Fields)- (POnumber,podate,supplierID,createdby,creteadon,modifiedby,modifiedOn,POTotal,POstatus..etc)

    PODetail(Major Fields) - (POnumber,linenumber,Item,qty,price,createdby,creteadon,modifiedby,modifiedOn,linestatus..etc)

    StockSummary(Major fields) -(id,locationID/Depot(if exists),totalstock,totalout)

    StockDetail(Your Transaction table)- (stocksummaryID,transactionlinno,docnumber/po#,transactiondate,qtyIN,Qtyout,systemdate..etc)

    after this everything is your programming logic to handle it.

    Put creteadon,modifiedOn defaullt as current date in table design.

    Hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    thanks for your reply ~, based on your design, how do I handle "Stock Subtraction" of the PO
    Please advice
    Tuesday, April 10, 2012 9:57 AM
  • Hi Handler,

    Sorry for late reply...Please correct me if i mis somthing ...

    for stock subtraction you have to pass a transaction in stockDetail entry (Balance entry)...QtyIn(qty under particuler enteity)..and QTYout (-tive/+tive ) based on your transaction type.. 


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    • Marked as answer by iHandler Thursday, April 12, 2012 4:11 AM
    Wednesday, April 11, 2012 11:46 AM
  • I got it, many thanks ~~

    Thursday, April 12, 2012 4:11 AM