Best way to model a single user with multiple usernames

database-design

I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames.

The context is a social gaming website. This may result in a user wishing to have more than one username. For example, one username for playing within a team (prefixed or similar) and one for playing alone.

I've been trying to rack my brains over the best way to represent this in my database, so far I've come up with:

Attempt One

In this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

Attempt Two

I guess with this one, we're more or less guaranteed to have a username for each user, and the owned usernames table provides a list of usernames owned by the user. It feels better, but then there's duplicate information (the username is specified in two tables when active).

Anyone have better ideas of how I can structure this? Is there a common pattern for this?

Essentially, what I am envisaging is some way of providing functionality to my website of having one user per person, logging in with an email and password, and linking one or more usernames to that user.

I guess a 1:N relationship between user and username where N > 0. I'm just not sure about how to model this effectively. Or indeed, if this is a sign that my desired functionality needs re-thinking.

There will be no formal notion of a team, this was just an example where a person would maybe want more than one username.

Of course, we could simplify this and force people who want separate usernames to sign up multiple times. This would simplify the database a huge amount, but it'd be more convenient to the user to allow one login for multiple usernames.

I'm thinking of limiting this to start with to a maximum of 2 or 3, but I guess this doesn't change the design, just a front end limitation.

Best Answer

You have clarified that you will not implement the team aspect in your system, but I consider that keeping the suggested approach for such scenario in this answer is still pertinent since it can be a useful example for other seekers.

Along with that, I have included some other contexts in which you could offer the option of having multiple Usernames (or Nicknames, or Handles), and a possibility that takes into account the common ASP.NET arrangement for dealing with Users and Roles data. These elements may serve to establish some analogies with the informational requirements of your own business environment.

1. User Profiles and Team Player Nicknames

1.1 Business rules

According to my understanding of your description about the social gaming website, I consider that the following conceptual-level formulations are specially relevant:

  • A User may be a Player of zero-one-or-many Teams
  • A Team is made up of one-to-many Players, each of which must be a User

1.2 IDEF1X diagrams

Then, I derived from those business rules the two IDEF1X1 diagrams that are presented in Figure 1.

Figure 1 - User Profiles and Team Player Nicknames IDEF1X Diagrams

1.2.1 Option A

As you can see in such a diagram, there is a many-to-many (M:N) association or relationship between the entity types Users and Teams, which takes effect in the associative entity type called TeamPlayer.

The UserProfile.UserId PRIMARY KEY (PK for brevity) property migrates2 to TeamPlayer as PlayerId, a role name that I have assigned to UserProfile.UserId in order to make it more meaningful in the context of its corresponding entity type, and these properties must be connected via a FOREIGN KEY (FK) defined in TeamPlayer.

On the other hand, TeamPlayer.TeamNumber is associated to Team.TeamNumber by virtue of another FK reference.

With this arrangement, one given UserProfile can hold

  • one Username when acting as an individual, and
  • one Nickname when he or she is carrying out the role of a Player for each of the Teams in which the UserProfile in question has joined.

1.2.2 Option B

This diagram is quite similar to the one referred in Option A, but it has an important aspect that is different, since I have moved the TeamPlayer.Nickname property to a separate entity type, which is called Nickname. This configuration is useful in case the Nickname value of a Team Player is optional, i.e., TeamPlayers may choose to provide or not provide a Nickname.

1.3 Team Player Nickname uniqueness

The Option A daigram has an ALTERNATE KEY3 (AK) defined for TeamPlayer, which is composed of two properties: TeamNumber and Nickname. In turn, the Option B diagram exhibits an AK in the Nickname entity type that is made up of TeamNumber and Nickname, as well. In this manner, both methods would provide UNIQUEness for a Nickname value within the context of a determined Team instance.

If you want to prevent the same Nickname value from being repeated in several Team occurrences, then you should set up an AK comprised of only one single property, that is, Nickname. This approach is valid for option A and Option B.

Alternatively, if you want to have the Nickname property set as NULLable (i.e., optional), you could define a “filtered” (or “conditional”, or “partial”, depending on the platform of use) UNIQUE constraint in Option A for supplying Nickname uniqueness in relation to a Team but, as I see it, proceeding this way things would get less elegant, so to speak.

2. People, User Profiles and Usernames

I created some more IDEF1X diagrams (presented in Figure 2) depicting different scenarios in which a given Person can have multiple Usernames, aiming to broaden the scope of this post. They are shown in the following section.

2.1 IDEF1X diagrams

Figure 2 - People UserProfiles and Usernames IDEF1X diagrams

2.1.1 Option A

This option is representing the following conceptual business rules:

  • A Person may log in via zero-or-one UserProfile
  • A UserProfile has one-to-many Usernames

Thus, it is comparable to your first diagram, but I have added a Person entity type that is optionally connected to UserProfile via the UserId FK definition.

Another important addition is the Username PK, which is comprised of

  • a FK reference to UserProfile.UserId and
  • the CreatedDateTime property that represents the instant in which a given Username was inserted.

This allows you to get rid of the Username.UsernameId (or username.id, if you prefer) property.

In this regard, this excerpt from your question is very important:

So, in this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

Yes, you should establish a policy that states how many Username instances can have the IsActive property set to 'TRUE' with respect to a precise UserProfile, whether exclusively one Username that is “active” at a given point in time, perhaps two, three, etc., or none at all. As you know, each possibility demands distinct validation methods.

But, if a certain User can have multiple Usernames, I do not think that there can be a problem if such a User does not have any “active” Usernames, since the log in credentials are the Email and Password. In this case, when no Username is “active”, you could display some sort of a “default message” indicating that situation.

2.1.2 Option B

This method is very alike to Option A, but it is less flexible, since I have integrated the UserProfile credentials into the Person entity type. By proceeding this way, you would force every Person to have a LoginEmail and a Password.

Conversely, Option A permits you to retain Persons (or People) that are not related to the User context in a clean and separate manner, without employing NULLable (i.e., optional) properties.

2.1.3 Option C

The diagram displayed as Option C describes the statements that follow:

  • A Person may log in via zero-or-one UserProfile
  • A UserProfile holds zero-one-or-many entries in UsernameHistory

This method implies notions pertaining to the subject known as temporal databases. You may find my take on Scenario A and my response to Scenario B of relevance in this respect.

With this approach —once the schema depicted in this diagram is built on a certain SQL environment— you could retain the “active” Username in a, say, UserProfile table and all the “past versions” in a, say, UsernameHistory table.

As per the data manipulation factor, basically, every time that a UserProfile UPDATEs his or her Username, you save a copy of the last Username version in the UsernameHistory table. Each History Version is uniquely identified by a combination of the Date and Time datum in which such row was modified and, or course, the UserId FK that links the row in question with its respective UserProfile.

As in any relational database project, you should seriously consider using ACID TRANSACTIONS in order to guard your data integrity and consistency.

2.1.3 Option D

Let us suppose that, in your domain of discourse of interest, a Person can perform two roles: Administrator and Author. This is where Option D comes into play, and the next assertions are useful in delineating our informational diagram:

  • A Person may be zero-or-one Administrator
  • A Person may be zero-or-one Author
  • A Person may login via zero-or-one UserProfile

In this way, a Person holds his or her particular “active” Usernames in his or her specific UserProfile and, also, can possess a Handle in case said Person is an Administrator and another different Handle when carrying out the role of Author.

3. Possibility with the common ASP.NET Users and Roles approach

You mentioned via comments that you are using ASP.NET MVC as your application development platform and, although I am not sure if you are as well utilizing the common (or default) ASP.NET course of action for handling Users and Roles, this section can serve as a good reference assuming you have to deal with the typical conditions.

3.1 ASP.NET Users and Roles approach diagram

The approaches suggested in Figure 3 appear feasible since you would not need to modify the tables that are usually created in ASP.NET apps. As you can see, you would only have to create an additional table for retaining the Handles (or Nicknames, or Aliases, if you prefer) that a given User may (or may not) have in regard to a certain Role assigned to such User in your application program. In other words, the same User could hold multiple Handles, and the context for each Handle instance would be an exact Role performed by the User at issue.

Attaching a Handle column directly to the UsersInRoles table could be possible too, but it may have unfavorable consequences in the ASP.NET authentication and authorization processes, aspects that, as you know, are of the greatest importance for the security of any app, so this method would require especial research and testing.

Possibility with the common ASP.NET Users and Roles Arrangement

3.1.1 Option A

This method depicts a Handle table that is connected to the UsersInRoles table by virtue of a composite FK that consists of the UserId and the RoleId columns. Said Handle table has an AK set up for, precisely, the Handle column, which implies that a specific handle value* could only be INSERTed in one single row.

3.1.2 Option B

In this option I have portrayed a Handle table that also makes a reference to the UsersInRoles table by means of a FK composed of the UserId and the RoleId columns, but it shows a slightly different approach that displays an AK constituted by the RoleId and the Handle columns, which would open the chance to have the same handle value repeated in multiple rows, but only in combination with disctinct RoleId values.

Notes

1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

2 IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.

3 An ALTERNATE KEY is a property (or a combination of properties) that holds values that uniquely identify an entity type occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYs. In an IDEF1X diagram, they are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually declared in a logical-level SQL DDL design via (i) a single- or multi-column UNIQUE constraint along with (ii) one or more NOT NULL constraints.

Related Question