Email Confirmation and Recovering Passwords

Modern applications have plenty of authentication features beside registration and login. In this article we will take a look at how to design the database for two such features: email confirmation and password recovery.

Email Confirmation

What Is It?

Most people familiar with the Internet know what an activation email is. An activation email is sent to the user after he or she registers for an account on a website or web application and contains a link that will allow the user into the system. The email is primarily used to ensure that the email address provided by the user during registration is real and that they have access to it. For lots of websites email is the primary means of communication with the users, so it’s important to make sure that the given address is correct.

Email confirmation also helps in preventing identity theft. That is, someone using someone else’s email to register their account. This is particularly problematic if the system only allows a single account per email, as it means the legitimate owner of that email address won’t be able to make their own account.

The solution, then, is to create a method for the user to prove that they have access to the given mail address. That’s where the activation email comes in.

How Does It Work?

First, the user successfully registers an account in a web application. The application then generates an activation token: a random, long string which it stores in the database. The objective behind the length is, of course, making it impossible to guess. The server then sends an email containing a link that includes the activation code to the registered email address. The link might look something like this:

https://www.mysite.com/activate?code=HbhUPq3i8w90Kdv4QtwiT2cVk3YoLq

Until the user clicks this link, he or she should either be unable to login or disallowed to make use of the features that being logged in would normally give access to since their accounts haven’t yet been activated.

The user then clicks the link, which directs him or her to a page that takes the activation code and looks for it in the database. The token should be unique, so that there is only one account associated with it. The system finds the account associated with the token, sets the account’s status to active and sets the token to null.

Alternatively, the activation link isn’t clicked and the user remains inactive. After a span of time decided by the developer, for example 72 hours after registration, the system automatically removes the user from the table.

How Do We Implement Email Confirmation Functionality in the Database?

How do all of these considerations translate to the database design? There are three columns related to the email confirmation process:

• email_confirmation_token – this column stores the confirmation token associated with the account. The column should be long enough to store the token (in our model the token is up to 100 characters so the column type is varchar(100). It is nullable since we set it to null upon confirmation of the account. Finally, the column is unique. We must be able to identify the account to activate just by the token.
• registration_time – we delete unconfirmed accounts after 72 hours so we need to keep track of the registration time.
• user_account_status_id – the status of the account. The possible statuses (such as EMAIL_CONFIRMED, or EMAIL_NON_CONFIRMED) are stored in a table user_account_status. The column is a foreign key referencing the table user_account_status. Putting possible statuses in a separate table allows to add new statuses if they are needed.

Password Reminder

This scenario responds to a very different necessity, but it is handled in a similar manner as email confirmation.

What Is It?

A password reminder system gives a user who has forgotten their password the chance to change his or her existing password to a new one. The standard way of handling this is, today, to send the user an email with a reminder token.

How Does It Work?

The user that forgets his or her password first clicks a link on the webpage which will redirect them to a form. Here, either the username or the email address will be requested by the system. The system generates a long, random token and stores it in the database together with a (short) expiry time. The expiry time is usually around 30 minutes. The system then sends an email with a link containing the token. The link in the email might look similar to this:

https://www.mysite.com/resetPassword?code=b6dcQVNw6REQ8rUs6TyKYtU48plQ9o

When the user clicks the link, the system finds the password reminder token in the database. If the token hasn’t expired yet, the user is redirected to a form allowing him or her to enter a new password. The password is changed and the reminder token is set to null again.

How Do We Implement It in the Database?

The user_account table will need two new columns:

• password_reminder_token – the password recovery token. The setup is similar as for the confirmation token: the column has to be long, to store the token (varchar(100) is our model), it has to be nullable, and it has to be unique.
• password_reminder_expire – how long the token is valid. It should be stored in a nullable column using the timestamp data type.

Result

After implementing these features, that is, password recovery and confirmation links, the database model for a simple online bookstore (designed in the previous article in this series) might look like this: