I have an inventory within a database set up as such
Inventory(Table) Links to an Inventory Lineitem(Table) it is very basic but nonetheless serves the purpose. I would like to add in an log register so, as things are added, changed etc. I can have a chronological log of what was changed and when, it would be helpful at year end.
What is the best way to handle this? Would I set up a new Table (call it Register) and just script in the changes as they occur? Or would a more dynamic link be better? If a dynamic link is better how would it look and what would it link to (Inventory(table) or the LineItem(table))?
One thought process that I would like to consider with this new log is when adding and subtracting inventory I would like to look at the costs of each time they have been entered and depending on the quantity needed to fill the order take them in the order of first in first out. Pulling the rights costs right along with it.
Just looking for general directional ideas I have tried a few different variants but am not real happy with any of them. Generally when I feel this way about my work I can look up and notice I missed the forest through all the trees.