I thought about an uncommon database structure and wonder if anyone has seen it in use before. It's basically using 2 databases:
- First database hold only the data that is currently valid
- Second database hold the history of everything that has ever been entered, updated or deleted in the first database
I'm working on a project where I'm required to log everything that happens and where the data changes frequently.
Example (not the real one)
You have to do the database design for a soccer league. In this league there are players and teams. The players often switch teams.
- First requirement : The database must hold the information necessary to play the next match. This means a list of every players, teams and in which team each player is currently.
- Second requirement : The database must hold historical values that we will use to generate statistics. This means the list of all the players that have been part of a team or the list of all the teams a player has been a part of.
These two requirements are kinda the opposite of each other. I have tried to do everything in the same database but it doesn't make sense. The first requirement only cares about "playing the next match" while the second requirement only cares about "generating statistics".
To do everything in the same database, I went with a sort of "insert only" database using the obvious soft delete to delete/update information...
What initially seemed like an easy task, holding a list of players, teams and the current team of each players, suddenly becomes a lot harder. The application logic required to play the next match is already complicated enough but now the database has a very unhelpful design where the application is required to add "is deleted" check on every single query just to play the next match.
Would you want to be that coach that yell "all players in the team, come to me" and then 2000 players come at you. At which point, you will probably yell "all players that are not deleted in the team, come to me" (while swearing about this stupid design).
I came to wonder why you need to put everything in the same database. Not only does the soft delete do a poor job at logging everything unless you add many columns (time_created, who_created_it, time_deleted, who_deleted_it) but it also complicate everything. It complicates the database design and it complicates the application design.
Also, I receive these 2 requirements as part of one single application that cannot be split but I keep thinking : this is 2 completely distinct applications. Why am I trying to do everything together.
That's when I thought about splitting the database in two. An operational database that is used only to play the next match and only contain the information that is currently valid and an historical database that hold all the information that ever existed, when it was created, deleted and who did it.
The goal is to keep the first database (operational) and the application as simple as possible while having as much information as possible in the second database (historical).
- Have you seen that design before? Does it have name?
- Are there any obvious pitfalls that I am missing?
You can basically think about the whole architecture as a 2 steps process.
Step 1 :
- Application is running and users are doing some actions
- Each time an event happen, it is recorded automatically (audit solution) in an event table
- Then the correct row, in the operational database is updated
Step 2 :
- A job read the latest insertion in the event table and insert this new data in the historical database.
- Users query the historical database to retrieve the information that they need.
Just from the event table, you can reconstruct the information to any point in time. The problem is that this event table is not easily queryable. This is where the historical database kicks in; to present the data into a way that it is easy to retrieve exactly what we want.
Additional problems when putting everything in the same tables
I have already expressed my concern about the added complexity of checking "is deleted" on each query. But there is another issue : integrity.
I make heavy use of foreign key and constraint to make sure that at any point in time, the data that is in my database is valid.
Let's look at an example :
Constraint : There can only be one goal keeper per team.
It's easy to add a unique index that check if there is only one goal keeper per team. But then what happen when you change the goal keeper. You still need to preserve the information about the previous one but now you have 2 goal keepers in the same teams, one active and one inactive, which contradicts your constraint.
Sure it's easy to add a check to your constraint, but it's another thing to manage and think about.