StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>We did this once by creating separate database tables that contained the data we wanted to snapshot, but denormalized, i.e. every record contained all data required to make sense, not references to id's that may or may no longer exist. It also added a date to each row.</p> <p>Then we produced triggers for specific inserts or updates that did a join on all affected tables, and inserted it into the snapshot tables.</p> <p>This way it would be trivial to write something that restored the users' data to a point in time.</p> <p>If you have a table:</p> <p>user:</p> <pre><code>id, firstname, lastname, department_id </code></pre> <p>department:</p> <pre><code>id, name, departmenthead_id </code></pre> <p>your snapshot of the user table could look like this:</p> <pre><code>user_id, user_firstname, user_lastname, department_id, department_name, deparmenthead_id, deparmenthead_firstname, departmenthead_lastname, snapshot_date </code></pre> <p>and a query something like</p> <pre><code>INSERT INTO usersnapshot SELECT user.id AS user_id, user.firstname AS user_firstname, user.lastname AS user_lastname, department.id AS department_id, department.name AS department_name departmenthead.id AS departmenthead_id, departmenthead.firstname AS departmenthead_firstname, departmenthead.lastname AS departmenthead_lastname, GETDATE() AS snapshot_date FROM user INNER JOIN department ON user.department_id = department.id INNER JOIN user departmenthead ON department.departmenthead_id = departmenthead.id </code></pre> <p>This ensures each row in the snapshot is true for that moment in time, even if department or department head has changed in the meantime.</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