How to storage history of a complex record

Question

I have in my relational database three tables like this: Route -> RouteStore -> Store. So I can modify which stores can be part of a route every certain time (adding or removing) and I need to keep a history of these changes. Someone I know suggested formatting the data into a text format (JSON this case) and save it into a new table.

The reason I doubt so much about using the JSON strategy is that if I am going to use the history data, I will always have to do programming stuff to decode de data before I am able to use it. Since the database is relational I would not have any advantage using any kind of query. Doing it this way I am aware that saving the data would be pretty simple, but using it would not be as much.

The usage of these data is crucial, since I have to create a bunch of different kind of reports from the history data which would end up turning into a nightmare.

Is there a better way to keep the history of the route modifications? What alternatives should I considere?

Answer 1

What you seem to be looking for is literally a history table for each table.

First, I'll assume some things about your tables to illustrate the pattern:

Route
- Id
- Name
- DateCreated
- DateUpdated
- CreatedByUserId
- UpdatedByUserId

You would create "shadow" tables that contain exactly the same columns, except they would have an additional "version" or "history sequence" column that acts as the primary key, plus another column defining what operation was done (Insert, Update or Delete).

You don't really want foreign keys to the real tables, if you are doing DELETE's, because you won't be able to delete the history. This implements full change tracking:

RouteHistory
- VersionNumber (Primary key)
- Action (string)
- Id
- Name
- DateCreated
- DateUpdated
- CreatedByUserId
- UpdatedByUserId

An INSERT into the Route table should correspond with an INSERT into RouteHistory:

• VersionNumber: 1
• Action: INSERT
• Id: (new Id of Route record, for instance 723)
• Name: (name of new Route record)
• DateCreated: ...
• CreatedByUserId: ...

Now when Route record #723 is updated, you would get another INSERT into RouteHistory:

• VersionNumber: 2
• Action: UPDATE
• Id: 723
• Name: (current name of Route)
• DateCreated: (current DateCreated of Route)
• DateUpdated: (current DateUpdated of Route)
• ... (repeat for each column in Route)

This can generate a lot of data in your database for transaction heavy tables, but it does give you every single modification to those tables. This is especially useful for auditing purposes.

The easiest way to sync the data in the main tables with data in the history tables is using triggers in the database, but this is not ideal if there are some database transactions where you don't want the trigger to execute... then again if you are going with full change tracking triggers guarantee accuracy.