Price History

A list of items / categories of items we need to keep track of price history for can be set as a flag in the master_stock table, the stock category table or in a separate table that has these as keys besides others.

Each item can be purchased from more than one supplier even on the same day. A table that has purchase date and stock_id as the primary key will not suffice. Hence it will need to be on a transaction basis that can be extracted through some involved SQL gymnastics.

What happens when some of these purchases are voided (and the specific case of being partially voided - some items and some quantities - short / over supply)? The SQL used will have to account for that as well.

Furthermore, manufacturing / assembly for stock issued from inventory should be disregarded in the SQL so hammered out.

FA keeps separate tables for computed data that relies on proper updation when the primary data is affected (voiding, editing, etc). This is done for speed of operation and non dependance on past accounting period data.

A modern ideal ERP would only rely on primary data and would hence need huge resources (storage space, computing power, RAM, thread slowdown and increased spawning, etc) and complex DB engines besides having an impact on archival based removal of old entries that will never become stale.

An intermediate approach would be to have some summary computed data made at the closing of each accounting year (aka P&L postings to Balance Sheet) and stored in separate tables much like opening balances. Fiddling with these values during subsequent years would
1. wreck havoc on the reporting systems and
2. hence on the decision making that depends on it and consequently
3. make for more difficult troubleshooting issues thereafter.