Table design in Azure Table Storage

Question

I should organize REST-service for messaging using azure. Now i have problem with DB. I have 3 tables: users, chats, messages of chats.

1. Users contains user data like login, password hash, salt.
2. Chats contains partitionkey - userlogin, rowkey - chatId, nowInChat - the user came from a chat.
3. Messages of chat contains partitionkey, wich consists of userlogin_chatId_datetimeticks (zevis_8a70ff8d-c363-4eb4-8a51-f853fa113fa8 _634292263478068039), rowkey - messageId, message, sender - userLogin.

I saw disadvantages in the design, such as, if you imagine that users are actively communicated a year ago, and now do not talk, and one of them wants to look at the history, then I'll have to send a large number of requests to the server with the time intervals, such as a week, request data. Sending the request with a time less than today will be ineffective, because We get the whole story. How should we change the design of the table?

Answer 1

Because Azure Storage Tables do not support secondary indexes, and storage is very inexpensive, your best option is to store the data twice, using different partition and/or row keys. From the Azure Storage Table Design Guide:

To work around the lack of secondary indexes, you can store multiple copies of each entity with each copy using different PartitionKey and RowKey values

https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/#table-design-patterns

Answer 2

Thank you for your post, you have two options here. The easiest answer with the least amount of design change would be to include a StartTime and EndTime in the Chat table. Although these properties would not be indexed I'm guessing there will not be many rows to scan once you filter on the UserID.

The second option requires a bit more work, but cleaner, would be to create an additional table with Partition Key = UserID, Row Key = DateTimeTicks and your entity properties would contain the ChatID. This would enable you to quickly filter by user on a given date/date range. (This is the denormalization answer provided above).

Hopefully this helps your design progress.

Answer 3

I would create a separate table with these PK and RK values: Partition Key = UserID, Row Key = DateTime.Max - DateTimeTicks

Optionally you can also append ChatId to the end of the Row Key above.

This way the most recent communication made by the user will always be on top. So you can later on simply query the table with passing in only the UserId and a take count (ie. Take Count = 1 if you want the latest chat entry from the user). The query will also be very fast because since you use inverted ticks for your row keys, azure table storage service will sort the entries for the same user id in increasing lexicographical order of Row Keys, always keeping the latest chat on top of the partition as it will have the minimum inverted tick value.

Even if you add Chat Id at the end of the RowKey (ie. InvertedTicks_ChatId) the sort order will not change and latest conversation will be on top regardless of chat id.

Once you read the entity back, you subtract the inverted ticks from DateTime.Max to find the actual date.