0

Disclaimer: I am a beginner in the field of database.

I am trying to design a database in PostgreSQL which will record the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM and Repository approach.

The simplified proforma is like this:

Items: Details of items we deal with.

Purchases: Records the details of items we purchased.

Sales: Records the details of items we sold.

Stock: Quantity of each Item in hand.

Now, I also have a stock table where current stock of each item is maintained. The dilemma I am facing is this:

When any Invoice is generated, it indicates that a sale has occurred, and the stock of each item in the Invoice is updated accordingly.

Similarly, when a purchase from our side occurs, we record it in the purchases table, and the respective items in Stock table has their quantity updated.

I would have to also consider the scenario of correcting the stock table if an existing invoice is modified or deleted.

These are the only three scenarios when the stock table would change.

Now, I can achieve this update of stock table using Triggers.

I can also use stored procedures in order to encapsulate the entry in Purchases or Sales table along with updating the stock table.

I can also do this in application logic using LINQ.

Which approach would be the most pragmatic one?

THE DETAILED STORY

Please allow me to discuss the entities that matter in this current context. We are a reseller. We buy materials in bulk, we process the materials and repackage them and then sell them.

In this table, the items we sell are marked as the entity ITEM. There is are two attributes in the ITEM table worth noting. One is MaterialType and the other NumberOfPieces.

The entity MATERIAL is the raw material we buy. Now suppose, we buy 100 pieces of pens, and we resell them in units of 1 or 5. So, in this case, the records in the item table would be

  1. Pens x1 (nickname) --- Pen (MaterialType) ----- 1 (NumberOfPieces)
  2. Pens x2 (nickname) --- Pen (MaterialType) ------5 (NumberOfPieces)

So, in PURCHASES table, we record the Material we have bought and the Quantity of it. With each purchase, we need to make three modifications: 1. Insert the purchase details in PURCHASE 2. Modify CurrentStock in MATERIAL(increase it by the amount we have purchased) 3. Insert the amount of increase in Materials in STOCKHISTORY table.

Please note that StockHistory does not record the cumulative stock, but just the amount of increase/decrease associated with each transaction.

Similarly, for each sale, we need to make three modifications. 1. Insert the sales details in INVOICE. 2. Modify CurrentStock in MATERIAL(decrease it by the amount we have purchased) 3. Insert the amount of decrease in Materials in STOCKHISTORY table.

Please note that INVOICE table here represents the invoices generated by us, i.e. the sales made from our end.

Here is the schema:

The Schema

Now, if we need to delete or modify any record of PURCHASES or INVOICE, we also need to modify the CurrentStock in MATERIAL as well as the corresponding records in STOCKHISTORY table.

Here is a rundown of STOCKHISTORY table:

  1. Material records the material whose stock has changed.
  2. Amount records the amount by which the stock has changed.
  3. Invoice records the invoice number of the transaction.

Now here is where it gets murky. And I need help with the schema here.

The FlowDirection records whether it was a purchase or a sale.

The PurchaseId records the invoice number of the purchase IF it was a purchase.

The SalesId records the invoice number of the sale IF it was a sale.

There is a constraint on the table where the nullity of PurchaseId and SalesId attributes are XOR'ed, i.e. One and only one of them will be null for a given record.

I am thinking of getting rid of the FlowDirection as it is obviously redundant. Secondly, I also will get rid of the Invoice attribute, as it is already recorded in PurchaseId/SalesId. Or, I can point Invoice column to a union of Num attribute of the INVOICE table and the Invoice attribute of the Purchase table (I have got to rename all these tables and attributes too). Which one should be a better approach?

2
  • May you kindly explain the advantages of using stored procedures and not trigger? Mar 7, 2017 at 22:46
  • 2
    Read up on double entry accounting. You can use it for widgets just as well as for dollars (or currency of your choice).
    – Joel Brown
    Mar 8, 2017 at 3:20

2 Answers 2

1

I have been working on same domain , and I appreciate the logic you trying to apply and schema also looks good just that material and item can be the same table as item will have no meaning without material and vice versa ,also add audit property to domain table as this will help to keep the track of changes as the system will grow multiple scenario will come in picture in production ex. Cancellation of order due to defect.

What I would recommend is create a event which will get fired on stock change / updated that will have old and new amount of stock so this event will be sufficient to defrentiate added / updated / reduced stock

And make purchases or all audit/stockhistory entity subscribe to this event so that for any reason the stock changes you don't need to make this calls and rest of the logic will be decoupled

0

Not sure about your specific domain, but typically in most inventory management it's going to be a little more complicated than that. Most businesses will take a physical inventory count on some kind of schedule and this may be as infrequent as annually. However, that physical will then become the new stock value, generating some kind of adjustment for the variance. In addition, sometime there is a separate release and receipt procedure which ensures that the change in inventory is booked to the correct inventory period. You should keep that in mind in any design, regardless of whether using triggers or not.

4
  • I have extensively explained my problem so that you can have an idea about the domain. And as for physical inventory count, actually, we need to keep a record of the stock in system and then check the stock physically biennially to see if there is any leakage of materials. Hope you understand. Mar 8, 2017 at 18:34
  • I think you can use triggers to make the entries when an invoice or purchase is finalized, but be aware that you might have to reverse these if an invoice is returned or canceled. When there is leakage/shrinkage, presumably a new physical inventory becomes the book inventory via some kind of adjustment to the history. In some systems I have seen, there is an inventory rollover process which establishes a new book inventory after every physical, with the physical generating an over/short which must be acknowledged first..
    – Cade Roux
    Mar 8, 2017 at 18:49
  • I tend to prefer systems where there isn't a physical stock number stored at all, but just a perpetual inventory which is equal to the beginning book value plus all the inventory movement of posted transactions since. If the calculation of that is time consuming for very large datasets, then optimize afterwards.
    – Cade Roux
    Mar 8, 2017 at 18:51
  • Actually, I have considered the calculated stock approach, and if you check the schema closely, it's possible to do that using the STOCKHISTORY table. But, as you already pointed out, it'll be time-consuming, and it would also mean that I'd not be able to delete any of the older purchase/sales records. Mar 8, 2017 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.