Infosys Microsoft Alliance and Solutions blog

« Connecting the Dots: Sizing & Percentage Fitment for Package Applications - A Perspective | Main | Silverlight Error 2103 »

Unique Column values in Azure Table Entity

Recently in one of our projects we had a requirement, where column TemplateName within an entity named Template should have unique values. Users were supposed to enter the value for TemplateName while creating a new template. Later on, saved templates were being used by all the users in the system. Obviously, duplicate template names would confuse users while selecting the specific template.  At a glance this seems to be a very simple and standard requirement.

In traditional RDBMS databases we would have created a column TemplateName as unique. In case of non RDBMS systems like Azure Tables, we start with querying the table to check if entered value for TemplateName already exists.  This looks simple to implement but the fact that values stored by Azure tables are case sensitive, makes it a little complex.
If we go by the approach of query, to check if the entered template name already exists then it would not be the logically correct solution. Due to the case sensitivity, the template names "AzureTemplate", "azuretemplate", "Azuretemplate" etc. will be considered unique, which could have confused the user while selecting the required template (e.g. AzureTemplate). We solved this problem by adding another column which stores Template Name value in UPPERCASE and referred the same column in the query for checking the uniqueness of the TemplateName value. The problem and solution are detailed below.


Problem Scenario:
Entity Name: Template
Earlier Structure: Entity Properties and Sample Values

PartitionKey (Literal)

RowKey (TemplateID)

TemplateName

TemplateDescription

Infosys

1

AzureTemplate

This template is used for Azure

Infosys

2

AmazonTemplate

This template is used for Amazon

Infosys

3

GoogleTemplate

This template is used for Google Apps

    In this case we used to query the table Template against column TemplateName to check if record with the entered template name value exists. If user enters azureTemplate or Azuretemplate, then the query would return as the template name doesn't exist in the table and user could create a template with that name.

Solution:
   To overcome this issue of case sensitivity in the values stored by Azure table we have added one more column to the entity Template called TemplateNameCAPS. At the time of saving the template, we stored TemplateName in the capital letters in column TemplateNameCAPS. Later on, in the query to check the uniqueness we used the column TemplateNameCAPS.

Modified Structure: Properties and Sample Values

PartitionKey (Literal)

RowKey (TemplateID)

TemplateName

TemplateNameCAPS

TemplateDescription

Infosys

1

AzureTemplate

AZURETEMPLATE

This template is used for Azure

Infosys

2

AmazonTemplate

AMAZONTEMPLATE

This template is used for Amazon

Infosys

3

GoogleTemplate

GOOGLETEMPLATE

This template is used for Google Apps

     Now, whenever user enters template name for the template to be created, we convert the entered values into upper case and query the table Template against column TemplateNameCAPS for checking its uniqueness.
     Now, whenever user enters template name for the template to be created, apart from storing the same value, we also convert the entered values into upper case and query the table against column TemplateNameCAPS for checking its uniqueness.
This helped us solve the issue of not allowing template names like "azureTemplate" or "Azuretemplate" when AzureTemplate value already exists in TemplateName column.
You might address the similar problem by not using another column but by converting the input to a specific case (lower, proper or uppercase) before saving it and use the same case for comparison.  We didn't do this to avoid changing the case of the template name value while storing and displaying it to the user. We wanted to keep the same case as what users have entered.
Hope you find this helpful in your projects too.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles

Infosys on Twitter