3

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?

  • 1
    To answer the question "which is best," we must first know what your criteria are for evaluating which solution is the best for your specific requirements. What are your specific requirements? For example, does the history need to be queryable? If it does, then the JSON solution is probably out. – Robert Harvey Feb 16 '17 at 22:14
  • You are right, adding it right away. – 8370 Feb 16 '17 at 22:15
  • 1
    Your concerns are valid, I would not recommend a JSON-based solution. However, more info is required to design a temporal model. I recommend you post your current schema for all 3 tables. Do you record temporal data in Route and Store or are they timeless? Do you need to keep track of what the whole route looked like at different times or just a log of additions and removals leading up to the current state? Describing some of the reports you have to create could also help to clarify the requirements. – reaanb Feb 19 '17 at 18:11
  • Look into 'event sourcing'. – Eternal21 Feb 21 '17 at 21:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.