As we get closer to wrapping up the initial development on MyColts.net and releasing a public beta I was working out the design and implementation for the "Colts Cred" system. The intent is to give away credibility points to people for various actions and help entice people to take desired action. The first trick is to figure out the actions that will result in points being given, which was pretty straight forward. I simply ran through the bulk of the functionality within the network and made a note of various actions (positive or negative) that could occur such as creating a blog entry, making a friend, commenting on a post of some sort, uploading photos, etc. I then assigned an arbitrary positive or negative point value to each action knowing they are going to change.
Then came the fun part. Working out a design that allows us to give away points in a way that won't chew up performance or add a ton of storage requirements. Additionally allowing the point values for actions to be point in time so that certain actions could be promoted through "double points" type offers. The solution to changing point values is pretty straight forward, which is just a name and value pair table. For each action there is a name assigned to it and a point value assigned in the table to that name. So when an action is taken the lookup is done based on the action name and points can be awarded or taken away based on the positive or negative value in the database.
The first inclination for the actual points was to just do an aggregate number that has total points and would be credited or negated with any action. That certainly satisfies performance by having a rollup number available for use at any time for any user, but testing would be next to impossible to verify accuracy. So we settled on a hybrid. There is an audit table that has a record of the profile, action, points given or taken away at the time, and a flag indicating if the points have been applied to the running total or not. There is a SQL Integration Services job that will periodically rollup the additional points, set the applied flag on audit records, and then update the aggregate total. We can baseline the audit trail at any point if it becomes too big by recording the point value for each profile at the point the audit trail was cleared and then capturing the audit trail from that baseline.
The additional benefit of all of this is the work was all pushed to the database without resulting in extra calls from the web servers. We simply added a stored procedure call to the end of most of the insert or update stored procedures, which took care of writing entries into the audit trail.
Now at some point I suppose we'll have to work out what exactly you can do with the points, but we've already had quite the competition in the testing team to see who shows up in the top of the Colts Cred list!
