Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multi version entity database design

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.

like image 695
DEHAAS Avatar asked May 24 '11 22:05

DEHAAS


People also ask

What is an entity in database design?

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.

What is version control in database?

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.


1 Answers

In data warehouses, this is generally solved using a Slowly Changing Dimension (SCD).

As a brief summary, you can:

  • Make a surrogate key (an instance id) and use the natural key and a version number (or effective date/time range) to keep track of changes (Type 2 SCD)
  • Have N columns in your room table for N versions (Type 3 SCD)
  • Have a separate "rooms_history" table and only keep the current instance in the rooms table (Type 4)

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.)

like image 158
Chris Morgan Avatar answered Sep 30 '22 05:09

Chris Morgan