Optimal SCD type 2 design

View previous topic View next topic Go down

Optimal SCD type 2 design

Post  kjfischer on Thu May 05, 2011 2:13 pm

I am wondering if there are advantages or an optimal way to design dimension? Specifically, is a single "effective_date" attribute ok? Or do you recommend a start_date and end_date? Is it necessary to have a "current_flag" indicator or can you just use the max(effective_date) to get that?

Take customer for example,

customer_id <-- surrogate key
custno <-- natural key
effective_date
cust_email


kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Optimal SCD type 2 design

Post  VHF on Thu May 05, 2011 4:14 pm

At a minimum you should have "begin effective date" and "end effective date" fields for each row. Designate a special far-future value such as 2999-12-31 to place in the "end effective date" field for current records. Any BETWEEN queries you do to pull records as-of a particular point in time will work OK, and you could use WHERE End_Effective_Date = #2999-12-31# to select current rows.

That said, many modelers also implement an "is current" indicator flag to faciliate identification of current rows.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum