title: Type II dimension joins
body: <p>I have the following table lookup table in OLTP</p>
  
  <pre><code>CREATE TABLE TransactionState
  (
      TransactionStateId INT IDENTITY (1, 1) NOT NULL,
      TransactionStateName VarChar (100)
  )
  </code></pre>
  
  <p>When this comes into my OLAP, I change the structure as follows:</p>
  
  <pre><code>CREATE TABLE TransactionState
  (
      TransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
      TransactionStateName VarChar (100) NOT NULL,
      StartDateTime DateTime NOT NULL,
      EndDateTime NULL
  )
  </code></pre>
  
  <p>My question is regarding the TransactionStateId column. Over time, I may have duplicate TransactionStateId values in my OLAP, but with the combination of StartDateTime and EndDateTime, they would be unique.</p>
  
  <p>I have seen samples of Type-2 Dimensions where an OriginalTransactionStateId is added and the incoming TransactionStateId is mapped to it, plus a new TransactionStateId IDENTITY field becomes the PK and is used for the joins.</p>
  
  <pre><code>CREATE TABLE TransactionState
  (
      TransactionStateId INT IDENTITY (1, 1) NOT NULL,
      OriginalTransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
      TransactionStateName VarChar (100) NOT NULL,
      StartDateTime DateTime NOT NULL,
      EndDateTime NULL
  )
  </code></pre>
  
  <p>Should I go with bachellorete #2 or bachellorete #3?</p>