I have to create an inventory database. I am looking for some design tips. I am trying to figure out how to store the data for transactions. The types of transactions will be:
I am leaning toward separate tables for Transfers, Shipping, Receiving, and Loss. However, I can definitely see a table with all the transactions in it with different Transaction Types.
I think both ways are viable, but I can't think of the pros and cons for each. Anyone with some insight they want to share? Other categories I might need? Other possible ways to store the transactions?
You are right in thinking that a single transaction table is the way to go, however, it would benefit you greatly to have separate temp tables for each of the processes, transfers,shipment, receipts etc...
Once the process has been completed, the results are transfered to the transaction table.
There are many different ways to setup an inventory application, if you search this site, you will come across many very helpful posts and links.
I have to admit to a "re-invention of the wheel" going on here.
Surely a bespoke would be more in keeping at this point?
All the functionality you are pointing to is a basis of a good POS/Warehouse DB.
Rather than going to the cost and effort of building why not buy in a package tried, tested and with support??
Too many reasons to mention but the major reason is a package will have too many features for us. We just need a glorified/automated spreadsheet, nothing more. We have part of the system built already. We will need to integrate with the system that determines our usage.
We are keeping track of direct mail pieces. Envelopes, insert, blank forms, etc. There are only about 50 different items. We only need to track incoming and outgoing quantities. An off-the-shelf inventory package will be overly complicated. No binning, warehousing, etc.