[TIP] Database Design: Point in Time Architecture and Soft Deletes

[TIP] Database Design: Point in Time Architecture and Soft Deletes


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.
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.

What is Point in Time architecture (PTA) and how is it used?
In a PTA design, a previous record can be modified without destroying the current record. Different user's might want to view the database from different point of in time, UserA might want to view the current database record, UserB might want to view last months's database record, and userC might want to view yesterdays database record.
The Problem
In an invoice application, your "Invoice" might have "Invoice_line_item" table that reference multiple product "items", once the invoice has been sent to your customer and has been paid. Sometime in the future, your product item code, name, description, might change, now if you did not use PTA design, when you make changes to your product items, it will also change your existing invoiced product items, that references the product items, this is not what you want to do.
You could however store the invoiced product item name, code and description etc into the "Invoice_Line_Item" table itself, but then you will be duplicating unnecessary data.
The Solution
In the case presented above, it is suggested that you use a PTA design, where no information is ever physically deleted from or updated in the database.
To track the changes in your PTA design, you would add the following columns, 
ValidFromDate - the date the record is valid from.
ValidToDate - the date the record is no longer valid.
CreatedBy - the user id that created the record (if you use EnterpriseManager, this would be the UserMasterId of the person that created the record).
CreatedDate - the date the record was created
OriginalRefId - the foreign key reference to the original primary key record.
Now, if you decide to change your product item, you create a new record, and Invoices referencing the previous product items remains untouched. Only the ValidToDate for the product item gets updated.
Soft Delete
If you want to have the ability to delete records, and still be able to track previous data, it is suggested that add a soft delete "DeletedDate" column, to flag the record as deleted.

DeletedDate - the date the record was deleted.



Thanks for sharing Robert, great article!

You might want to take a look into this extension by João Portela, Entity Track Changes - Track the changes in your application, that logs changes to records - not exactly the same architecture, but it's awesome for auditing.

Tiago Simões

Tiago, thanks for the link, looks good for auditing and tracking changes, however I think it serves for a different purpose :)