Insert Only Database - Pros and Cons

Explanation

In a traditional database updates and deletes are allowed, this destroys data which can sometimes be considered undesirable. In an ‘Insert’ only database or ‘Point in Time’ database only inserts can be performed.

Additional Fields Required

DateCreated – Date and time the record was created.

DateEffective – Date and time the record becomes effective, this can be different than DateCreated for numerous reasons.

DateEnd – Date and time the record is ceased to be effective.

DateReplaced – Date and time the record was replaced by another.

OperatorID or SessionID – User related to the creation of the record.

The date and time fields may also require a UTC offset field. Therefore a total of 9 fields are required.

How are updates done?

This is more complex than a typical update.

1. Locate the existing record.
2. Flag it as ‘ended’ and ‘replaced’ with the current date time or the time the update will be applied.
3. Insert a new record and copy some of the existing field values and the new field values.

Pros and Cons

Pros:

• Rollback to a point in time is possible.
• Triggers are not required.
• All changes are logged, it is not possible for a field to be added that is not included in the audit.
• Less Locks.
• Make data changes that do not ‘Go Live’ until a specified date and time.

Cons:

• Table size is significantly larger if a lot of record changes are required. Additional 9 fields required for every table.
• Need views on all tables.
• If replication is a requirement all audit database is also replicated on replication servers.
• No option to exclude fields from the audit.
• Making a change (typically an update) is more complex. 
• Foreign key and relationships can be more complex.