2

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?

Improve this question
1
  • are you looking for AuditTrail type of implementation?
    – Yusubov
    Aug 27, 2013 at 17:57

1 Answer 1

Reset to default
3

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:

  1. UserCode: Application user unique identifier. (mandatory)
  2. TransactionCode: Any CRUD operation will need to have a unique transaction code (like GUID) (mandatory)
  3. ChangeDate: Transaction date. (mandatory)
  4. EntityName: Entity (table) that is being manipulated.(mandatory)
  5. ObjectId: Entity that is being manipulated primary key.
  6. FieldName: Entity field name.
  7. OldValue: Entity field old value.
  8. NewValue: Entity field new value
  9. OperationType: 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
Improve this answer
2
  • This is exactly what I'm looking for....I imagine the usercode would be the primary key. Could I use the transactioncode as the changeset? Also, how do I handle different datatypes?
    – Javalsu
    Aug 27, 2013 at 17:58
  • Good to know it works for you! The usercode stands for userId, in order to track who made changes. OldValue and NewValue can be a string(varcharmax) fields to capture the values.
    – Yusubov
    Aug 27, 2013 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.