What if you never delete data? An article about Point in Time Architectures

Thu, 01 Mar 2007 19:16:00 GMT

Arthur Fuller, of Redgate, has written an article for the RedGate Simple Talk newsletter about database design for systems that have to preserve data - that don't delete data, but mark it as no longer current.

http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/

Point in Time Architecture (PTA) is a database design that guarantees support for two related but different concepts – History and Audit Trail.

History – all information, both current and historical, that as of this moment, we believe to be true.
Audit Trail – all information believed to be true at some previous point in time.

The distinction is that the Audit Trail shows the history of corrections made to the database. Support for History and Audit Trail facilities are notably absent from typical OLTP databases. By "typical", we mean databases that support the traditional Select, Insert, Delete and Update operations. In many cases, typical OLTP databases are perfectly fine for their requirements, but some databases demand the ability to track History and Audit Trail as core requirements. Without these abilities, the database will fail.

Typical OLTP databases destroy data. This is most obvious with the Delete command, but a moment's thought reveals that the Update command is equally destructive. When you update a row in a table, you lose the values that were there a moment ago. The core concept in PTA is this: no information is ever physically deleted from or updated in the database.

Karen Lopez: Musings on Data, Process, and Architecture

What if you never delete data? An article about Point in Time Architectures