Azure tables have some advantages over RDMS tables. But there are some challenges in dealing with these tables because of their NoSQL nature:
- Table can have only two keys: Partition Key and Row Key
- There are no “foreign keys”, which makes normalization a bad approach
Let’s take a look at two scenarios that addresses these challenges.
Here is a typical structure of the Customers table in RDMS:
The most typical approach to define the structure of the data in the RDMS is data-centric: first, the structure of the table is defined based on the data, second, the usage of the data (based on the application functionality) is analyzed and table is altered accordingly, usually in form of additional indexes.
With Azure tables the approach should be usage-centric: the functionality of the application and the way the data is used should define the structure of the table.
In our example with customer data, quick analysis of the application shows that most of the time users will do a search for a customer by name when they fill out invoices. Application assist in this task with auto-complete functionality: after typing first 3 letters of the customer’s name, application will suggest a list of the customers:
Here is one of the possible ways to store Customers in the Azure table that addresses this usage scenario:
First three letters of the customer’s name are used as the Partition Key. Some unique identifier is used as the Row Key (this is Id field from the relational table). This structure provides fast access to the list of the customers by first 3 letters of their name.
This is not the “best & only” solution and it has it’s issues, but it demonstrates that designing Azure (or any NoSQL) tables requires a different approach comparing to the RDMS world.
