title: SQL Server - Logical Question
body: <p>I have the following table:</p>
  
  <pre><code>Id INT/PK
  UserId INT/FK
  CreatedDate DATETIME
  ActivityMarker INT/FK
  </code></pre>
  
  <p>The table can be written to (incl. record updates) and deleted from (e.g. today I can delete a record from 1/1/2010). The CreatedDate contains the date the record was created. ActivityMarker and UserId can be updated.</p>
  
  <p>What I need to do is keep a historical record of how many records, daily, for a given UserId X, had ActivityMarker Y. I need this information to chart for a user how many items for a given marker they had across time.</p>
  
  <p>I understand that the current table structure doesn't support this kind of historical information; once somebody changes the value of ActivitiyMarker or UserId, the previous value is gone forever, and I don't know when the change happened either.</p>
  
  <p>Can someone suggest the best way to keep this information so I can, in the end, get the count of records for a given UserId and ActivityMarker as it was on a specific day? </p>