The following describes the user account structure used for a sports pool site supporting pools in a number of different sports leagues. This is intended to provide a possible starting point or generate alternative ideas for developing a user account structure for other projects. Note that all sensitive information should be securely stored in an encrypted format.
The basis for the structure developed for the sports pool site was:
-
There is one Account Name per account (e.g. "rdswift"). These Account Names are unique to the entire site database (including all sites). Each account is assigned an Account UUID identifier at the time of creation, which never changes and is used to internally identify the account. This allows the user to change the Account Name and/or Password as part of their login credentials if they choose to do so, while still maintaining the rest of the account settings. Changing an Account Name might be in response to the Account Name being compromised. The Account Name and Password are not displayed publicly anywhere, and are only used for login purposes.
-
Each account can have multiple email addresses, and each address must be validated. There is always one email address that is designated as the "Primary" address, which is the destination address used when sending validation email messages or to contact the user in the event of problems. The user can change the Primary address used for the account by selecting one of the other previously validated email addresses linked to the account through the Account UUID.
-
Each of the available pools has its own associated email subscriptions. Each email address can be subscribed to any of the available available subscriptions, and the selected subscription information is linked to the email record (which is linked to the owning account through the Account UUID).
-
Each account can have multiple Players. This is to allow the separation of the displayed name from the account login name, and to allow different player names to be displayed for the user for each of the available pools/sites, if the user desires. Player names are unique to the entire site database (including all sites). Each Player name is assigned a UUID at the time of creation, and this UUID is linked to the owning account through the Account UUID. This allows the user to change the Player Name displayed if they choose to do so, while still maintaining all links related to the player.
-
All activity logging includes both the Account UUID and Player UUID (if the activity is associated with a Player) to be able to trace it back to the appropriate entity regardless of possible prior or subsequent name changes to either the account or player.
-
Each new account request must include an ID Name, Real Name, email address and any other required information for the account, and utilizes a CAPTCHA to help avoid the automated creation of spam accounts. The email address provided is automatically set as the Primary email address for the account, and is used as the destination for the validation email message.
-
All appropriate requests (e.g. account creation/deletion, adding/removing an email address, or changing an account's ID Name or Primary email address) are stored (along with the request timestamp) in a separate "requests" table pending validation, and a validation email message is sent to the Primary email address for the account. When adding an email address to an account, the validation message is sent to the email address being added rather than the Primary email address to avoid the addition of an address not under the user's control. Requests are not processed and removed from the requests table until they have been validated by following the instructions included in the validation email. Requests that have not been validated within the set time period are automatically removed from the requests table.
