Let's say you have a
FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.
Then, I would create a
FOO_HISTORY table. This has all the columns of the
FOO table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from
FOO. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the
*_HISTORY tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not put
RevisionDate into the primary key).
Now, every time you update
FOO, just before you do the update you insert the old values into
FOO_HISTORY. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.
If you want to delete a row from
FOO you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging
FOO as deleted.
This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in
FOO itself. Every query then needs to check those dates.