Table Design of Purchase Order

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

Answer 1

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/

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

I got it, many thanks ~~