How well does this work when you audit multiple tables with foreign key relationships between them?
If the user references a customer, and you want to find out what customer the user belonged to at time X (but the customer might have been deleted in the mean time), you have to track through the history of multiple tables to find answers. More if you have n2m relationships...
It sounds doable, but not trivial to get right for more complicated models.
---
Update: to clarify, I want to be able to present a somewhat homan-readable history of an object without doing much table-specific logic.
If an author can publish many books, you typically have an n2m mapping table, like book_authors. If you want to add newly published books to the "author" history view, you have to determine than you have to include parts of the history of the book_authors table. I don't think you can do that through a join though? Trying to think my way through this...
Maybe this can be dealt with easier if you can assume each table has a non-composite primary key, and include that primary key in the history table, and then you can do joins more easily on the history?