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?