I'm working on a web project that involves user-editable content, and I'd like to be able to do version-tracking of the actual content, which lives in a database. Basically, I want to implement wiki-style change histories.
Doing some background research, I see a lot of documentation about how to version your database schema (mine is actually already controlled), but any existing strategies about how to track your database content changes are lost in the avalanche of schema versioning stuff, at least in my searches.
I can think of a few ways to implement my own change tracking, but they all seem rather crude:
- Save the entire row on each change, relate row back to source id with a Primary key (what I'm leaning towards currently, it's the simplest). Lots of small changes could produce a lot of table bloat, though.
- save before/after/user/timestamp for each change, with a column name to relate the change back to the relevant column.
- save before/after/user/timestamp with a table for each column (would result in too many tables).
- save diffs/user/timestamp for each change with a column (this would mean that you'd have to walk the entire intervening change history to go back to a certain date).
What is the best approach here? Rolling my own seems like I'm probably reinventing someone else's (better) codebase.
Bonus points for PostgreSQL.