UPDATE: I don’t usually claim something as an original idea. Anything formed in my brain is just an amalgamation of the videos I have watched and the stuff I have read. For this idea of just creating a JSON field in each table, I don’t remember seeing it anywhere else before. That is why I thought it was neat when I was perusing the Octopus tables today, and I noticed they also include a JSON field in each table. They even went as far as to make theirs Not Null, which I didn’t do.

Others can argue when to use Sql vs a NoSql solution. There is a time and place for each. I have went down both paths and there usually comes a time where the grass looks a little greener on the other side. Sql is great for creating related tables and helping to ensure referential integrity at the database level. However, it can be a little cumbersome to add one simple field to an existing table. Even if you are doing migrations from code, there is some work involved.

On the other hand, NoSql is great at handling this “jagged” data. You simple add new properties to your json object and store it. However, the problem I have had with NoSql in the past is that you have to put more thought into your data design up front to know how you will want to pull it out. Often, ad hoc queries require a full table scan. Some of these things may no longer be true, but who can keep up with how quickly things change around us?

Anyway, in some recent work, I stumbled across an approach that I think combines the benefits of both approaches. Others have probably been doing some version of this for years, but it just clicked for me the other day. The “trick” is to create a VARCHAR(max) field where we can store a json object. This gives me most of the relational goodness of Sql along with the ability to easily add “minor” fields, without touching the database. This will not work for “major” fields that are a key to another table or something that is going to be searched often. However, for something like adding a link to store the gravatar on a user record, this can be pretty sweet.

Finally, some code. Here is the script I use to create a table. The key is line 21, where I add my JsonData field. Any table that might need “extended” in the future gets this new field.

Here is the C# code for the Person object.

public class Person : EntityBase
{
    public long PersonId { get; set; }
    public long TenantId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public Person_NoSql NoSql { get; set; }

    public Person()
    {
        NoSql = new Person_NoSql();
    }

    public string JsonData
    {
        get { return JsonConvert.SerializeObject(NoSql); }
        set { NoSql = JsonConvert.DeserializeObject<Person_NoSql>(value); }
    }

    public class Person_NoSql
    {
        public string UrlAvatar { get; set; }
        public string AnyOtherField { get; set;}
    }
}

The code to use the new property is pretty simple.

Person person = mgr.GetPerson(personId);

if (!ReferenceEquals(null, person))
{                       
    FirstName = person.FirstName;
    LastName = person.LastName;

    UrlAvatar = person.NoSql.UrlAvatar;
    AnyOtherField = person.NoSql.AnyOtherField;
}

I am using Dapper for the data layer. When it sees a JsonData field returned from a query, it maps it to the JsonData string property on the object. As far as Dapper knows, this is just another string field.

That is pretty much it. I thought this would be a longer post, but in the words of the great “Forrest Gump”, That’s all I have to say about that