Change Management¶

How to design a table or system so that it can keep historical changes in database ?

Imagine you have a load table, which has the following fields:

1. Use a History Table
2. Use an Audit Table
3. Event Sourcing — the pattern for storing data as events in a log and using them to reconstruct its state —

Option 1: Use a History Table¶

What if I want to keep a whole record as a history but doesn't want to affect my actual table? Then you may want to create a history table for load, which has the same fields as load table itself.

LoadHistory

Then a user with guid 52f80bf6-3589-461c-a0f7-e48073305b99 modifies the ibus to 218 and stat to 1

Now, you only have to insert one record, which is a duplicated record of the one in Load table, into LoadHistory table, which looks like this:

Lastly, update the original record in Load table into

In this case, you are trying to save the old record completely into another table and then apply changes in the original table. It's better if the users want to have a full picture of how the record is changing.

However, the down side of this approach is that, redundant information is stored. For example, if you have a large number of fields, but only one or two fields are updated every time, it's actually a huge waste of space.

Option 2: Use an Audit Table¶

A better solution is to create an audit table to record every single change in every field, which saves the spaces by eliminating redundant information. The table looks like this:

NetworkChanges

For example, there is a transaction looks like this:

And now, user with guid 52f80bf6-3589-461c-a0f7-e48073305b99 modifies the ibus to 218 and stat to 1

Hence, there are 2 fields changed, ibus and stat. Respectively, there should be 2 records added into the audit table as shown below.

The downside of this approach is the possible huge increase of records. Since every change in different fields is one record in the Audit table, it may grow drastically fast such as tens of changes resulting in hundreds of audit records. In this case, table indexing plays a vital role for enhancing the querying performance.

Option 3: Use of Event Sourcing¶

Event Sourcing is a design pattern that aims to record all state changes (events) in the system and build the current state of the system from these events.

This allows the historical state of the system to be completely reconstructed and each state change can be undone, queried or analyzed.

If you are familiar with chess, you know that if you keep a record of the moves made by each side, and when the game is over, you can easily see how it went. It is even possible to go back to any T moment of the game by recording these moves. Recording these moves in chess is Event Sourcing in software terms.

The chess example is the conscious choice. It is similar to the way we manage our grid model. Chess is a game with certain rules, just as in a network there are rules for the topological interconnection of network elements. Board state, while referring to the network topology chess pieces can also be thought of as network elements.

In this example, we'll focus on a specific aspect of the game, the movement of pieces, and ignore other rules and factors for simplicity.

1 Initial State: At the start of a chess match, the board is empty. We create an event called GameStarted to represent this initial state.

{
  "type": "GameStarted",
  "board": [],
  "turn": "white",
  "castling": true,
  "enPassant": null,
  "halfMoveClock": 0,
  "fullMoveNumber": 1
}

1. Making a Move: When a player moves a piece, we create an event with the relevant details. For example, moving the white pawn from E2 to E4 would be represented as:

{
  "type": "PawnMoved",
  "from": "E2",
  "to": "E4",
  "pieceColor": "white",
  "newBoard": [
    ...,
    ["P", ".", ".", ".", ".", ".", ".", "."],
    ...
  ]
}

1. Applying Events: To view the game state at any point, we can apply the events in the order they were created. When a new event is added, we take the current state (represented as the board), apply the changes from the event, and update the state accordingly.

2. Replaying the Game: Because we have all the events that led to the current state, we can replay the game by applying the events in reverse order. This allows us to recreate any point in the game's history.