I’m working on a site where companies can create a profile then add locations for each profile.
Those first two tables are simple enough.
But any changes to their profiles or locations have to go through an approval process. This is where I’m torn.
The end result would be an employee going through a worklist and seeing the requested changes and clicking “approve” or “deny”. Approve applying the new data to the existing record, deny setting a flag on the request record(s) and sending a response.
What I’m wondering is the best way to go about the change requests. I would definitely like to keep the change requests in a separate table than the profile or location table.
I can’t decide if it’s best to just create duplicates with a few extra columns of the profile and the location tables and use those for change tracking.
Or if I should just create a simple table with just the columns to capture the change target id, field, and value, plus a few extras like changeset, datetime, flag, etc.
What do you think?
1
From the viewpoint of database design (ignoring the database features and the application architecture ) I will prefer having a table for audit trail (change history) having changes per Entity and per field by implementing a flat table called Trail_History with no foreign key to any table, columns will be:
UserCode: Application user unique identifier. (mandatory)TransactionCode: Any CRUD operation will need to have a unique transaction code (like GUID) (mandatory)ChangeDate: Transaction date. (mandatory)EntityName: Entity (table) that is being manipulated.(mandatory)ObjectId: Entity that is being manipulated primary key.FieldName: Entity field name.OldValue: Entity field old value.NewValue: Entity field new valueOperationType: CRUD operation discriminator. (mandatory)
Adopting this approach will be beneficial for:
- Any entity (table) could be traced
- Reports will be readable
- Only changes will be logged
- Transaction code will be the key point to detect changes by a single action
2