CODEWITHSUNDEEP
sql-serverdatabasedatabase-designsql-server-2012event-sourcing
Dave New
Dave New

Reputation: 40072

Historical, versioned and immutable data

We need to add data auditing to a project.

We could create some kind of Log or Audit table to record the changes in our SQL database. But would it not be a better idea to have the data in the database to be immutable. So, instead of updating existing values, rather add a new time-stamped row. This way ALL changes are tracked.

We are using the repository pattern, so this can provide a means to completely abstract this immutability/history/versioning away from client code. Our repositories consist of the basic CRUD operations (add, update, delete, find/gets). The following changes would need to occur:

Other approaches:

My only gripe with the above is that, if the data had somehow got bad, multiple rows could be returned for a given date and time.

Is there any major flaw in this design or is there something I could have done differently? Is there perhaps a formalized approach the the above?

Is this somehow related to event sourcing?

Upvotes: 4

Views: 1579

Answers (1)

dean
dean

Reputation: 10098

My take on this:

You will lose the ability to create unique constraints on the data, except the identity columns.

Also, complicate FK handling. What happens when you update a parent row? It's the insert, thus new identity, but the child rows still reference the "old" record.

Performance will suffer.

I will advise to create a separate table for the archive. You can simplify the updates using the OUTPUT clause with UPDATE, and inserting into archive in same statement.

The approach you're describing is more appropriate for a DWH then an OLTP database.

Upvotes: 2

Related Questions