Back to articles list
Inputting a username and password is one way of accessing an account, but it’s not the only one. In this article, we’ll see how to enable external services (like Google or Facebook) when logging in to a database.
What Are External Service Logins?
Giving a user the option to access their system accounts through external services is a growing trend among web designers. This option can provide several benefits, such as giving users one less name-and-password combination to remember. It can also help administrators personalize user experiences.
When web applications offer an external service login, the login screen looks like the picture below. A user can enter their login and password, or they can click a button which will redirect them to the service of their choice (Facebook, Twitter, Google, etc.) where they will log in and be redirected to the original application.
Here is a sample login screen from the Vertabelo Academy:
How External Logins Work
Adding external login services requires some additional work from the developers. Most popular social media services use a protocol called OAuth 2.0. Only Twitter uses an older protocol called OAuth 1.0. The protocol enables the reading of user information like their full name, email, gender, etc. The exact information available depends on the social media service and whatever the user provided. (For example, it’s possible to have a Facebook account without an attached email address.) Regardless, though, we’ll focus on how to implement this system in the design of a database.
As a starting point, we’ll be using our previous article on password recovery and email confirmation as a base. Here are the user-related tables from this article.
Designing a Database for External Logins
A lot of information in the user_account table is related to handling authentication – plus related features like password reminder and email confirmation – on our own. We don’t need these columns if the user authenticates with an external service. The password reminder, email confirmation and other features are handled by the external service. The first step in our design is to separate the user_account table into two tables: user_account and user_profile.
The user_account table now handles all its own authentication bookkeeping. The user_profile table represents the actual user information: name, email, time zone, terms of service acceptance, and so on. All business tables should now be related to the user_profile table.
Now to the external accounts. The OAuth protocol, in the end, gives us an identifier for the user in their system. This identifier is not the user’s name in the external system. It is just an identifier for our application. We have to store this internal id in our database. We could add nullable columns facebook_id, google_id, twitter_id, etc. to the table user_profile. But we will do something different.
We will add new tables: facebook_account, twitter_account, google_account, which will store the external id. This way, if we need to we can add additional information specifically for each social website. If we want to add a login possibility for another social website, we won’t have to change the user_profile table. We will only add another external_service_account table.
Each of the new tables shares the same column format. One of them will be int user_profile_id, which is both a foreign key referencing the id column in the user_profile table and the primary key. (This can serve as a primary key because no two accounts in our system should be associated with multiple accounts of the same external service.)
The other column will be the id of the external account – facebook_id, for instance. There is a unique constraint on each of the facebook_id, google_id, and twitter_id columns. We know no two accounts are given the same id. In fact, when the user is authenticated with an external service we know their facebook_id. When we find the corresponding row in the facebook_account table and find the right user_profile, we know which user just logged into the system. If there is no row with this id, we create a new row in the user_profile table, and a new row in the appropriate account table.
Here is the final model:
