I am trying to decide on the best method for audit logging within my application. The main reason for the log is reporting the sequence of events (changes).
I have a hierarchy of Objects, I need to create reports when something changes on any part of that hierarchy, at a latter date.
I think that I have three options:
- Have a log for each table and therefore matching the hierarchy of objects then creating a view for the report.
- Flatten the hierarchy and de-normalise the table, making reporting easier - simple select statement.
- Have one log table and have a record for each change making reporting harder but more flexible to changes.
I am currently leaning towards option 1.