4

There are two strategies when dealing with data records which contain multiple versions. One is to have current records in one table, and their past versions in a history table.

The other is to have all versions in the same table with a flag on the current version.

I've seen arguments for each, and I've seen community agree with both strategies in different posts.

If there is no right answer, how does one decide which strategy to take? I can see the advantage of keeping all the records in the same table for query simplicity (all the records in one table) and less tables in general.

The flip side is you have these huge tables with a ton of records depending on how many versions and complicated relationships between tables.

Pro History Table Database Design for Revisions? Database history changes, same or different table?

Pro Current Record flag https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table

Other Articles https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table

Community
  • 1
AnotherDeveloper
  • 143
  • 1
  • 5

1 Answers1

6

If we're explicitly discussing Slowly Changing Dimensions in a Data Warehouse scenario and you're following the Kimball methodology, there's actually more than 2 different ways of doing this.

The Kimball methodology has 7 different ways of recording SCDs (scroll down to Slowly Changing Dimension Techniques in the link). Types 1, 2 and 3 are apparently the most common, I don't really know of anyone using 5, 6 or 7.

The history table being Type 4, is the one I currently use. It allows us to keep the 'live' table as compact as it can be. We rarely query the history tables, so they're really just there for when we need to audit the history of a dimension. There is no relationship between this table and any others and it has a surrogate ID as the Primary Key.

I've worked in places where we've used Type 2, which is the Flag method and it can lead to unnecessarily large table growth on a key table. However, that's just my experience!

Mark Sinkinson
  • 10,657
  • 4
  • 47
  • 54