Design to Handle multi-parameter details in Windows Azure Table Storage Accounts

Question

I am designing a web application - Dashboard - to display the data \ graph on the UI from Table Storage.

We have for now around 20 parameters expected to get into the Table Storage - each parameter has a related value, quality & time_Received associated with it.

This app should be built in a way it should accommodate better when there are

1. new parameters getting into Storage Table

2. new clients involved - which means storage account may be different but with the same app to do the similar activity for another client for different set of parameters

My Query

Option1

Should I have one table for each parameter (columns - Value, Quality, Time_Received, PartitionKey, RowKey, Timestamp) with different Table names say Param1_Table, Param2_Table ... OR

Option2

Should I have a single table, with all parameter values in the same table, (same columns = Value, Quality, Time_Received, PartitionKey,RowKey) - which can be differentiated with PartitionKey (like param_!_PK,param2_PK...) and rowKey

Expected Behaviour of the app

Not all parameters will need(or allowed) to be subscribed by an user The system should behave in a flexible manner that there should be option for an user to select\deselect a parameter for display. So at one point of time the system as configured should display the data\Graph of the selected Parameter details from the Table Storage.

Answer 1

In Azure Table Service, the PartitionKey and RowKey values are indexed to create a clustered index that enables fast look-ups and Azure Table Service doesn't allow us creating any secondary indexes. So it is recommended that you make full use of PartitionKey and RowKey. We can use these two columns to store the data we need to query frequently.

For example, you could save parameter-name as PartitionKey and parameter value as RowKey if you only create one table for all the parameters. You could save parameter value as PartitionKey and quality as RowKey if you create one table for each parameter. Creating one table for each parameter will make the one extra column(quality) be indexed.

Other advantage of creating one table for each parameter

Multi tables help you to logically organize your entities, it could help you manage access to the data using access control lists, and you can drop an entire table using a single storage operation.

Advantage of creating one table for all the parameters

You can get cross-parameters data in one query. If you require atomic transactions across entity types, you need to store these multiple entity types in the same partition in the same table.

Based on your description, creating one table for each parameter will be better for your scenarios.

In this case as u have mentioned if I give param value as PartitionKey(PK) and quality as RowKey(RK) at point of time wont it be likely to have a duplicate? being that =>Expected value for Parameter_Value will be in a number range =>Expected value of quality is Good, Bad ... Is it so?

Setting param value as PR is just a demo which show you the differences between choosing one table or multi tables.

To choose which column as PK and RK depend on whether the values combination of these columns are unique and what query statement will be used to query data from your table. Since PK and RK are indexed, query according PK and RK will be executed efficiently. For example, if the table is often queried by parameter value and time_Received, you'd better set these columns as PK and RK if the values combination of these columns are unique.