SQL Server - Logical Question

I have the following table:

Id INT/PK
UserId INT/FK
CreatedDate DATETIME
ActivityMarker INT/FK

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. ActivityMarke开发者_如何学Cr and UserId can be updated.

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.

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.

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?

Keep a record of the changes by using effective dating.

or

Use a trigger to audit to another table

or

Take a look at SQL 2008 audit feature (may have something for you in there, but I'm not sure it will be in a friendly feature).

Effective dating is where you keep all your records and mark one as being the currently active one either through some kind of date logic or a flag. Here is an article explaining the concept - http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/ .

Auditing with triggers is fairly common and you should be able to find loads of info about it.

SQL Audit is a new feature in 2008 - looks like it dumps interactions on an object to a log - might just store t-sql statements and userid, not actual values of the columns.

How about writing snapshots of the records to a history table? If you are working right in SQL Server you could use triggers. If you are working w/ an ORM tool, some have the ability to intercept changes.

You'd need a table like this

Id INT/PK   -- alternately UserId + Rundate could be the PK
UserId INT/FK
RunDate DATETIME
AMCount INT

Then run daily:

INSERT INTO HistoricalActivityMarkers( UserId, RunDate, AMCount)
   SELECT UserId, GETDATE(), count(*)
   FROM ActivityMarkerTbl
   GROUP BY UserId, GETDATE()

Since you only want a daily record, there is no need for triggers, which would record every change, and would also fail to give you a daily record if a cahnge isn't made at least once a day.