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
- Pens x1 (nickname) --- Pen (MaterialType) ----- 1 (NumberOfPieces)
- 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:
- Material records the material whose stock has changed.
- Amount records the amount by which the stock has changed.
- 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?