Best way to model a single user with multiple usernames

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:

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?

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.

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.

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.

1. 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

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 ce

StackExchange Database Administrators Q#120530, answer score: 6