I have the need to develop an application that will use postgres as the
back end, and most of the design has been worked out, but I've one issue
left to resolve and want help in this. If this is not the appropriate forum
for this type of question, please point me in the right direction.
For several reasons (including operational and legal) once data are
entered in a table they cannot be changed or deleted without an audit trail
of the change, when it occurred, who made the change, and the reason for it.
Tables might contain laboratory or instrument measurement values or the
names of regulatory staff.
My current thoughts are that there needs to be a separate table, perhaps
called 'changes', with attribute columns for the source table, identifying
value(s) for the original row, new value, date of change, person making the
change, and the reason for the change. The original table should have an
attribute flag to indicated that a row has been changed.
The middleware of the application needs to check this table when data are
to be viewed in the UI and present only the current row contents. A separate
view would display a history of changes for that row.
All thoughts, suggestions, and recommendations based on your expertise and
experience will be most welcome.
TIA,
Rich