Table Storage design for Identity

Question

I am creating an ASP.NET MVC application with Identity and I decided to go with Azure Table Storage since it's very cheap and fast for my needs. The application will have two types of accounts: single user and multi-users with one owner. So I modeled this:

public class User : TableEntity, IUser
{
    /// <summary>
    /// The Email of the owner of the account
    /// </summary>
    public string Id { get { return PartitionKey; } set { PartitionKey = value; } }
    /// <summary>
    /// The Email of the user of the account
    /// </summary>
    public string UserName { get { return RowKey; } set { RowKey = value; } }

    //other fields not relevant
}

I am wondering how to make this effective for logging in and searching for a user, however. The Store for Identity has this:

public async Task<User> FindByIdAsync(string userId)
{
    try
    {
        TableResult operation = await UsersTable.ExecuteAsync(TableOperation.Retrieve<User>(userId, userId));
        return operation.Result as User;
    }
    catch (Exception)
    {
        return null;
    }
}

If the user trying to log in is the owner of the account, this will be a Single Point query and, according to the docs, have the best performance.
However, if the user is not the owner, that would result in a full table scan since a PartitionKey would not be available to filter on it, so the query would have the worst performance.

The other idea I have would be to make the user the PartitionKey and the owner the RowKey, but that would make it every single search a Range Query and have less performance than the Single Point queries.

How could I best design this? This is a new app, so there is no existing user base.

A few clarification questions: 1) What exactly is this Id? Is it some value that uniquely identifies a user or is it some value that identifies the account? 2) What's the format of this Id? Is it a GUID value or something that user specifies (like a login name)? 3) How are the users authenticating themselves in your application? Do you have your own user store where users are authenticated by username/password or have you implemented some kind of external identity store like Azure AD/Microsoft Account etc.? 4) How do you differentiate between a single user & multi-user? — Gaurav Mantri
@GauravMantri 1 and 2: look at the code for the model, ID is the email of the owner of the account (who purchased the account). 3: Users login through a Email/Password form in the application. 4: This is not yet implemented. — Camilo Terevinto
@GauravMantri For sure, this is an app I'm making at home on my spare time so the design isn't final — Camilo Terevinto

Gaurav Mantri Gaurav Mantri · Accepted Answer · 2016-04-18T19:23:03

A few recommendations I would like to make (based on the app we've built which also uses Table Storage exclusively).

Introduce a new entity called Account or Subscription. You can use a GUID to uniquely identify an account.
Along with user information (user id, password etc.) in the user's table, store the account information as well. So at all times when you're authenticating a user, you're making point queries. Also store the user's role (Member or Owner) in the user's table. Doing this way, you would know if the user is an individual user or part of a team. If the user is part of a team, then what the role of that user in that team (Owner or Member).
Store 2 copies of user data. First copy is what you're using now. Second copy essentially associate a user with an account where the PartitionKey of the second copy is the account id and row key is the PartitionKey of the first copy. This will help you find the users in a particular team.

Individual User Login Scenario

So an individual user logs in using their username/password. You retrieve the information about the user and there you find out that the user is an individual user based on the account type.

Team Owner Login Scenario So when a team owner logs in, based on the login you will find out that the user is a team owner. If the team owner needs to find the list of all the users in the team, you do another query (it will be the PartitionKey query only) which will give you information about all the users in the team.

Team User Login Scenario

So when a team user logs in, based on the login you will find out that the user is a team user. Since the user is a team user, he/she need not query the table to find out about other users in the team. Even if they do, you will do another query (same as team owner query) to get the list of users in a team.

Table Storage design for Identity

1 Answers