We have need to track certain database field history tracked and report over the time i.e. Let's say there is an Account table with field Status (Active/Inactive).
Account status may go to Active to Inactive and vice-versa over the time.
We need reporting at account level to see how long an account remains Inactive in a time period i.e. a Month
We are debating couple of options here for database tables to track the data,
[Timestamp] [Field] [Old Value] [New Value] - More generic but tough to generate reports without data transformation.
[Timestamp] [Status] [Inactive Start Time] [Inactive End Time] - Easy to generate reports without any data transformation.
Option 1 looks more like audit. Option 2 looks more like report. I'd point you into that direction - which one do you prefer?
If you choose option 1, then you are close to philosophy of Domain Events architecture. In that theory, you just store system events, things that have happened, with all low-level details that lead to them. Then you calculate everything that is the result you want to have - for example, different reports can be constructed from the stream of events, though you have to crack data to reach that stage.
If you choose option 2, then you are close to philosophy of CQRS architecture. There, you keep separate read-only schema (Q represents the query part of the system). Data in the query storage is easy to read, i.e. it is formatted in the way which is easy for the view to render.
Now you see what is more important to you in your system. Do you want to be able to reconstruct the sequence of events that happened (auditing is important)? If yes, then select option 1. Do you want to be able to easily display the report, without caring how things happened? If yes, then select option 2.