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
Teams, which takes effect in the associative entity type called
UserProfile.UserId PRIMARY KEY (PK for brevity) property migrates2 to
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
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
Username when acting as an individual, and
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:
Nickname. In turn, the Option B diagram exhibits an AK in the
Nickname entity type that is made up of
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
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
CreatedDateTime property that represents the instant in which a given Username was inserted.
This allows you to get rid of the
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
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,
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
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
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.
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.
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
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.