Slowly Changing Dimension Designs: Date Range with Parent

In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Basic Table

This is the basic setup I’ll be using (single line formatting for compactness only):

We’re going to deal with the fact that Pear Computers has changed its name over the years and we need to know what it was on a Point In Time basis.

Date Based with Parent

In this method we add To and From dates to our table in order to show when a value was active, but instead of adding an arbitrary surrogate key into our table we add a ParentCompanyID. For example, our company table changes as follows:

Summary (main points, not exhaustive)

• You can easily maintain a Primary Key / Foreign Key relationship
o Both CompanyID and ParentCompanyID
• Easy to find latest record via toDate
• Aggregation at companyID and parentCompanyID is simple

I’ve actually left Cons empty here, not because there aren’t any as I’m sure some people will have found several, but because there aren’t any that I consider glaring and hugely cumbersome. Therefore this tends to be the method I’ve leant towards in the past. You just need to ensure that you heavily index the Dimension tables in order to accommodate the several likely access routes.

Leave a Comment

Your email address will not be published. Required fields are marked *