I am currently working on a project in which we need to keep multiple versions of a given entity in the database.
For example let’s assume we have an entity called House which has rooms. Now whenever a room is added, updated or deleted from the house, or a property on the house or any of the rooms is changed, we need to keep both the old and new information. Preferably we would like to group changes together in a “change set” (really the problem is sort of like source control).
Now the actual model is quite a bit more complicated than this, but I think this example illustrates the problem.
I would very much like to hear some possible solutions to this problem.
We are running Microsoft SQL Server 2008 R2.
An entity in DBMS (Database management System) is a real-world thing or a real-world object which is distinguishable from other objects in the real world. For example, a car is an entity. An attribute of an entity gives us information about the characteristic features of an entity.
Database version control is the practice of tracking every change made to the database by every team member. Like application version control, database version control acts as a single source of truth. It empowers you with complete visibility, traceability, and continuous monitoring of the changes in your database.
In data warehouses, this is generally solved using a Slowly Changing Dimension (SCD).
As a brief summary, you can:
My team has traditionally used a combination of Type 2 (for more "recent" data) and Type 4 (for "older" data in an archive). We group changes using a change_id
in the objects being changed that is a reference to a change
object that includes the date of the change as well as other metadata (user, comments, etc.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With