Optimal SCD type 2 design

[kjfischer]

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

[VHF]

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.