Advice needed regarding design of database schema keeping record of current stock

Question

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:

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?

Answer 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

Answer 2

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.