Windows Azure : Static reference data (part 1) - Representing simple static data in SQL Azure & Representing simple static data in the Table service

2/21/2011 8:53:41 AM
Every application typically has some sort of frequently accessed static reference data. This data is usually very small and typically used for data normalization purposes. Let’s return to the Hawaiian Shirt Shop website and look at an example.

For each shirt displayed in the Hawaiian Shirt Shop web page, you might wish the customer to be able to specify the following criteria about the shirt they want to buy:

  • Shirt personage type (men, ladies, boys, girls)

  • Shirt size type (small, medium, large, extra large)

  • Shirt material (cotton, silk, wool)

As you can see, the data listed is fairly static, and it’s applied across all shirts. All shirts will have a size and a material (admittedly not wool). This data can be considered static, because it’s unlikely that it would ever be changed once it’s defined. (Hawaiian shirts are unlikely to suddenly start being made in platinum.)

Figure 1 shows a page of the website where that data can be selected. The customer can browse shirts that are designed for men or ladies, and for a particular shirt they can choose the size or material (style).

Figure 1. Product detail page of the Hawaiian Shirt Shop website

As you can see from figure 1, the web page represents the static material (style) and size data with drop-down lists, whereas the personage type is represented as a hyperlink that will perform a search for different shirt types. For now we’ll focus on the two drop-down lists (material and size).

The first question you’re probably asking is, “Where and how do we represent this data?” Let’s take a look at how this could be done using each of these technologies:

  • SQL Azure

  • Table service

  • Cache

We’ll start with SQL Azure, as this is probably the most familiar way of representing data.

1. Representing simple static data in SQL Azure

SQL Azure is a relational database, so you would use a typical relational model to store the data. Figure 2 shows a database diagram for the Hawaiian Shirt Shop website in SQL Azure.

Figure 2. A database with Shirts, SizeTypes, and Materials tables in SQL Azure with no relationships defined

In figure 2 you can see that the data for each of the drop-down lists (size types and materials) is currently stored in their own tables. As of yet, we haven’t defined any relationships between the static tables and the Shirts table (which is the most central table in the relationship).

Now let’s take a look at how you would retrieve data from this database and populate the drop-down lists on the web page.

Populating Drop-Down Lists

To populate the materials or size types drop-down list directly from a database, you can make a standard ADO.NET call to the database (either using ADO.NET directly or your favorite data-access layer technology, such as Linq2SQL, ADO.NET Entity Framework, or NHibernate).

The following code shows how you could bind the size drop-down list using ADO.NET directly:

DataSet ds = new DataSet();
using (SqlConnection conn =
new SqlConnection(mySqlAzureDBConnectionString))

using (SqlDataAdapter da =
new SqlDataAdapter("SELECT SizeId, SizeName FROM SizeTypes",

sizeDropDown.DataSource = ds.Tables[0];
sizeDropDown.DataTextField= "SizeName";
sizeDropDown.DataValueField = "SizeId";

The preceding code shows that you can bind your drop-down lists to a table in SQL Azure just as easily as you can with a regular SQL Server database.


The preceding code obviously isn’t up to production standards. You shouldn’t mix data-access code with presentation-layer code, but it does illustrate the point.

Cost issues with SQL Azure

SQL Azure uses a fixed-price model, and if your database is tied up servicing static data calls (which always return the same set of data), you may hit the limits of your database quickly and unnecessarily, requiring you to scale out to meet the demand. In this situation, caching the data is probably the most cost-effective approach.

As stated earlier, SQL Azure isn’t the only method of storing static data. Let’s look at how you could store this data using the Table service.

2. Representing simple static data in the Table service

Just as easily as each type of static data could be represented in a SQL Azure table, the data could also be represented as entities in a Table service table. The following C# class could represent the SizeType entity in the Table service.

public class SizeType : TableServiceEntity
public string SizeCode { get; set; }
public string Description { get; set; }

In this class, the PartitionKey for the SizeType entity isn’t relevant due to the size of the table, so you could make all entities in the table have the same partition key. You could use the SizeCode property to represent the RowKey.


Because the Table service isn’t a relational database and you don’t need clustered indexes here, you have no need for the SizeTypeId surrogate key that’s present in the SQL Azure implementation.

The following code represents the data service context class for the SizeTypes table.

public class SizeTypeContext : TableServiceContext
private static CloudStorageAccount storageAccount =

public SizeTypeContext()
: base(storageAccount.TableEndpoint.ToString(),

public DataServiceQuery<SizeType> SizeTypeTable
return CreateQuery<SizeType>("SizeTypeTable");

To store a size in the SizeTypeTable table in the Table service, you could use the following code:

var sizeTypeContext = new SizeTypeContext();

var newSizeType = new SizeType
PartitionKey = "SizeTypes",
RowKey = "Small",
SizeCode = "S",
Description = "A shirt for smallish people"

sizeTypeContext.AddObject("SizeTypeTable", newSizeType);

This code will store the “Small” size entity in the SizeTypeTable table.


In this particular example, the SizeTypeTable table will never grow beyond a few rows, so it’s not worth splitting the table across multiple servers. Because all the size data will always be returned together, you can store the data in a single partition called SizeTypes.

Once you have a fully populated SizeTypeTable table, you can bind it to the drop-down list using the following code:

var sizeTypeContext = new SizeTypeContext ();
sizeDropDown.DataSource = sizeTypeContext.SizeTypeTable;
sizeDropDown.DataTextField = "RowKey";
sizeDropDown.DataValueField = "SizeCode";

In this example, the drop-down list is populated directly from the Table service.

Cost issues with the Table service

Because you’re charged for each request that you make to the Table service, reducing the number of requests will reduce your monthly bill. In a website where you receive 1,000 page views of the Product Details page, this would translate to 4,000 Table service requests. Based on these figures, your hosting bill could get very costly very quickly if you follow this model. Caching the data is probably the most efficient thing to do in this situation.

  •  Performing Granular Backup Using the SharePoint Central Administration
  •  Using SharePoint Central Administration for Backup and Restore
  •  Backing Up and Restoring a SharePoint Environment : Using the Recycle Bin for Recovery
  •  Using Non-Windows Systems to Access Exchange Server 2010 : Understanding Other Non-Windows Client Access Methods
  •  Using Non-Windows Systems to Access Exchange Server 2010 : Remote Desktop Connection Client for Mac
  •  Using Non-Windows Systems to Access Exchange Server 2010 : Configuring and Implementing Entourage for the Mac
  •  Using Non-Windows Systems to Access Exchange Server 2010 : Mac Mail, iCal, and Address Book
  •  Parallel Programming with Microsoft .Net : Futures - Variations
  •  Parallel Programming with Microsoft .Net : Futures - Example: The Adatum Financial Dashboard
  •  Parallel Programming with Microsoft .Net : Futures - The Basics
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer