Currently I want to structure a tracking/history table like this:
- PrimaryKey - ID
- OtherTableId - fk
- fieldName - name of the field its tracking
- OldValue
- NewValue
- UserName
- CreateDateTime
So basically I want to have a table that will track another tables history, store the column name of the changed field with the new and old value. My question is can anyone poke holes in this? Also, what is the easiest way to ensure that only a column name from the tables its tracking is entered into the fieldName column? Currently my options are to have a enum in the service I'm building, or create another status table and make the fieldName an fk. Any better ideas?
Edit Goal: There are currently only 2 fields that we care to track. One field will be shown on a web page to display history, the other field will only be accessed by one department and they have access to a view of the database which they’d be able to query. They’d be querying just this one field to get information on who changed the field and what to. This is the reason we wanted to set it where a database field defines the table column rather than having an exact copy of the table record history. We only want two fields tracked with the possibilities of adding or removing fields in the future.
Thanks!