StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's say you have a <code>FOO</code> table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.</p> <p>Then, I would create a <code>FOO_HISTORY</code> table. This has all the columns of the <code>FOO</code> table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from <code>FOO_HISTORY</code> to <code>FOO</code>. 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 <code>*_HISTORY</code> 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 <code>RevisionDate</code> into the primary key).</p> <p>Now, every time you update <code>FOO</code>, just before you do the update you insert the old values into <code>FOO_HISTORY</code>. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.</p> <p>If you want to delete a row from <code>FOO</code> you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging <code>FOO</code> as deleted.</p> <p>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 <code>FOO</code> itself. Every query then needs to check those dates.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload