Regarding Azure table design

Question

I am working as freelancer and right now working on one of my game and trying to use Azure table service to log my user moves in Azure tables. The game is based on Cards.

The flow is like this:

Many users(UserId) will be playing on a table(TableId). Each game on the table will have a unique GameId. In each game there could be multiple deals with Unique DealId. There can be multiple deals on the same table with same gameId. Also each user will have same DealId in a single game.

Winner is decided after multiple chances of a player.

Problem:

I can make TableId as PartitionKey and but I am not sure what to chose for RowKey because combination of TableId and RowKey (GameId/UserId/DealId) should be unique in the table. I can have entries like:

TableId GameId DealId UserId timestamp 1 201 300 12345 1 201 300 12567

May be what I can do is to create 4 Azure tables like below but I am doing a lot of duplication; also I would not be able to fire a a point query as mentioned here at https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/#guidelines-for-table-design

GameLogsByTableId -- this will have TableId as PartitionKey and GUID as RowKey GameLogsByGameId -- this will have GameId as PartitionKey and GUID as RowKey GameLogsByUserId -- this will have UserId as PartitionKey and GUID as RowKey GameLogsByDealId -- this will have DealId as PartitionKey and GUID as RowKey

Thoughts please?

Format of TableId,GameId,DealId and UserId is long.

I would like to query data such that

1. Get me all the logs from a TableId.
2. Get me all the logs from a TableId and in a particular game(GameId)
3. Get me all the logs of a user(userid) in this game(GameId)
4. Get me all the logs of a user in a deal(dealId)
5. Get me all the logs from a table on a date; similarly for a user,game and deal

Answer 1

Based on my knowledge so far on Azure Tables, I believe you're on right track.

However there are certain things I would like to mention:

You could use a single table for storing all data

You don't really need to use separate tables for storing each kind of data though this approach logically separates the data nicely. If you want, you could possibly store them in a single table. If you go with single table, since these ids (Game, Table, User, and Deal) are numbers what I would recommend is to prefix the value appropriately so that you can nicely identify them. For example, when specifying PartitionKey denoting a Game Id, you can prefix the value with G| so that you know it's the Game Id e.g. G|101.

Pre-pad your Id values with 0 to make them equal length string You mentioned that your id values are long. However the PartitionKey value is of string type. I would recommend prepadding the values so that they are of equal length. For example, when storing Game Id as PartitionKey instead of storing them as 1, 2, 103 etc. store them as 00000000001, 00000000002, 00000000103. This way when you list all Ids, they will be sorted in proper order. Without prepadding, you will get the results as 1, 10, 11, 12....19, 20.

You will loose transaction support

Since you're using multiple tables (or even single table with different PartitionKeys), you will not be able to use Entity Batch Transactions available in Azure Tables and all the inserts need to be done as atomic operations. Since each operation is a network call and can possibly fail, you may want to do that through an idempotent background process which will keep on trying inserting the data into multiple tables till the time it succeeds.

Instead of Guid for RowKey, I suggest you create a composite RowKey based on other values

This is more applicable for update scenario. Since an update requires both PartitionKey and RowKey, I would recommend using a RowKey which is created as a composition of other values. For example, if you're using TableId as PartitionKey for GameLogsByTableId, I would suggest creating a RowKey using other values e.g. U|[UserId]|D|[DealId]|G|[GameId]. This way, when you get a record to update, you automatically know how to create a RowKey instead of fetching the data first from the table.

Partition Scans

I looked at your querying requirements and almost all of them would result in Partition Scans. To avoid that, I would suggest keeping even more duplicate copies of the data. For example, consider #3 and #4 in your querying requirements. In this case, you will need to scan the entire partition for a user to find information about a Game Id and Deal Id. So please be prepared for the scenario where table service returns you nothing but continuation tokens.

Answer 2

Personally, unless you have absolutely massive data requirements, I would not use table storage for this. It will make your job much harder than using an SQL database; you can use any index you like, have relational integrity, and so much more. The only thing in favour of ATS is that it's cheap for large data.