For a service management/inventory/provisioning application I once wrote, I used timestamps for version numbers on all principle records. I also kept a log of committer, comments and an automated overview expression of the change.

As you suggest, I had views that presented current state and others that presented the full history. It was simple (crude even) but surprisingly effective. So much so and so subtly/profoundly so that several attempts to replace it through the years were aborted when they realized the replacements weren't as helpful as the original. It was all Perl/PostgreSQL - no triggers or stored procedures. A real developer (I'm just a sysadmin) who supported it after I left said it was very easy to understand but I was obviously not a developer because I hadn't used any of the tools that would have made it easier. But I ramble...

I have often wished for the structure of a relational database with the version/update history of repositories like cvs and git. I wish I could add a time or version to my queries to pull out the way it was. The concept is simple but I have never seen anything like it or made it myself. But then I haven't put much effort into finding such a system.