Setup a partition and row key for user account database in Windows Azure

Question

I'm just a little bit stumped at the moment on a small issue. I want to setup an accounts table where all the registered users data lives. This table will contain stuff like email, addresses, SRP values for passwords, etc.

The small problem I have is how to setup the row and partition keys? I thought about having the region in the partition key, but then again, if the user changed regions etc, how will I know how to query it, because as far as I know you need both the row and partition key to query for something.

Thanks for your time

Answer 1

For Table Storage you should always consider your query and update patterns first, then design your key scheme based on that.

Will you need to query on anything other than UserName? Perhaps Email? If so you might want to include that in the key.

Will you ever need to update multiple users in a batch? Then they would need to be in the same partition.

If you only ever need to operate on one user at a time, and you'll always have the username to query on, then just use UserName for the PartitionKey and be done with it.

Answer 2

If you username is fixed and can not be changed ever. Then a simple way to solve this problem (the way I solved it) is to have first two or three characters of username as partition key. So this way your data can be partitioned. And searching something would be easy too. Compare PartitionKey and RowKey. If user gets username wrong. You can tell them immediately that something was wrong.