I am building a relational database-backed Line-of-Business application for my consulting client (or rather, I'm rewriting it - long story). This software product is a SaaS service for a niche industry. In this case, as far as we're concerned, it's a system which manages reports for woodchucks - it generates woodchucking reports for each woodchuck employed by a woodchucking organization.

My client is very keen to add gamification elements to the system, specifically he would like to add an achievements system which can easily be extended without needing to add one-off rules throughout the existing codebase (a C# 6.0 ASP.NET 4.6.1 MVC 5 application, with a multi-tenancy SQL Server database). Confounding things, the database has other dependent applications, so I cannot change the database schema without a good reason. This complicates the problem of needing to store additional small pieces of data arbitrarily, given at least some form of referential-integrity will be needed.

As an side, I personally support adding such a system because it drives user-engagement and steers the user towards using the system appropriately, in case we feel a particular feature is under-used or over-used. One example achievement the client wants to add is getting a trophy for sending 100 emails to customers (the SaaS users' end-customers) a month, another for changing your user account's settings to something other than the default (e.g. timezone, color scheme, etc). Not all achievement data can be determined by running offline queries against the database because many business operations happen without touching the DB, such as sending emails.

In games powered by engines like Source or Unreal, then achievements can be implemented by using whatever in-engine scripting services are available and simply hooking into pre-existing game events, thus decoupling the achievements system from the engine codebase or even the "game"/mod codebase. Whenever an achievement script detects some achievement being made it simply logs it locally and/or fires-off a web-service request to Steam/Xbox/etc's achievement service.

To achieve something similar in a LoB application I would need to create many event-hooks everywhere in the application (a huge task in itself), and then create a new project to consume those events and implement the achievement logic. But that doesn't solve the problem of data-storage - at least with key/value and JSON stores the arbitrary data can be serialized and attached to any existing object without any problems, but we can't do that in SQL Server - a new db schema would need to exist to hold special tables that would often only hold one-off pieces of data - it seems very wasteful and inappropriate.

I know Visual Studio had an achievements system for the 2012 release ( https://visualstudiogallery.msdn.microsoft.com/ed9e23e2-ff50-43e3-9aa6-c91f27bd6a6d/ ) - but they have the benefit of being able to store data arbitrarily.

  • What do you mean you can't store arbitrary data like Json in SQL Server? Ever heard of the XML data type? SQL server has one. I've seen it used to store data that frequently changes its schema. Not sure I recommend it, but you can certainly do it. – RubberDuck Aug 6 '16 at 12:07
  • Not without changing the schema, and the XML type doesn't enforce referential integrity. – Dai Aug 7 '16 at 1:40
  • you can still fire global events and catch those events in an "achivement manager" then save achievements locally for that user in a json or xml file. – Mightee Nov 6 '17 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.