What is the proper way of storing a changelog, similar to git, for data in a database.

Red lines are removed, green lines are added, dark green text are changes inside a line.

Currently, I have it set up so that every line in a table is given a unique string id that is used to determine where an edit occurs. When a user wants to change the value in column juice from Apple to Orange, it logs the date, time, row id, column name, column initial value, column requested value, and some user information.

The request log looks something similar to this:

[Timestamp] [Username] [RowID] [Column] [CurrentValue] [RequestValue] [Reason] [Reviewed]

And the values look something like this:

[2016-04-27 12:00:00] [Jack] [JPMmaaevz] [juice] [Apple] [Orange] [I like oranges more] [false]

You can understand this feels a little messy. Is there a better way of handling change requests in a database?

  • If you're looking to keep an entire history of data changes then maybe Event Sourcing is what you want. It's a pretty big investment, though. – MetaFight May 2 '16 at 16:09
  • Have you taken a look at couchdb changes feed? – Esben Skov Pedersen May 2 '16 at 16:15
  • CouchDB does look interesting, but I prefer something SQL-based. It'd take a good bit of rewriting to switch now. – Auxesis May 2 '16 at 16:22
  • 3
    The first question to ask is how how you plan to use the auditing and how much of it you need. There's a huge difference between replay/undo functionality versus "I need to know who to blame for legal reasons" functionality. – Darien May 2 '16 at 16:58
  • 1
    OK, so it sounds like you're developing a feature to track pending/approved/denied change-requests against some kind of business-object, rather than a pervasive logging infrastructure for all kinds of changes. Does that sound right? – Darien May 3 '16 at 1:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.