Develop User Management Feature with Social Login – Part1

Author: zemna
Date: April 17, 2021
Share this:
  • Part1 – System Analysis & Database Design
  • Part2 – Internal User Management
  • Part3 – Integrate With Social Login

Part1 – System Analysis & Database Design

1. System Analysis


User can register using signup feature. User can login using user credentials. If user forget password, user can reset password using registered email. User can login using social account like Google, Facebook and Twitter.


The big flow of this feature likes following picture.

User Management Big Flow{: .img-responsive}

When user connect to website, user can login using basic login system and also social login. Each login feature have different business logic.

Basic Login

Process is like as traditional one. User register using username, email, password, display name, etc. After register, user can login using username(or email) and password. System also can give a feature to reset password for user who forget password.

Social Login

User also can login using their social account like Google, Facebook and Twitter. Social login has three steps to process. Authorize app by social webpage, verify user in user database and connect social account with user database.

2. Database Design

Let’s make a database for user management.


User Management ERD{: .img-responsive}

There are 3 tables to manage user. I divided table by feature group.

‘user’ Table

This is the main table of user. All users have one record in this table. When user login using social login feature, system searches user table to check existence and add record if doesn’t exist.

`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`email` varchar(50) NOT NULL,
`password` varchar(128) DEFAULT NULL,
`display_name` varchar(50) NOT NULL,
`created` datetime NOT NULL,
`user_group` int(11) NOT NULL,
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username` (`username`)

‘user_password_reset’ Table

This table is used for reset password. I divided this table from user table because of frequency. If user want to reset password, system add a new record to ‘user_password_reset’ table with temporary reset verification key and verification expiration date. This key will be sent to user via email. User can click password reset link from email and set a new password. After complete to set a new password, record in this table will be deleted automatically. User also can’t be set a new password if link is already expired.

CREATE TABLE `user_password_reset` (
`user_id` bigint(20) NOT NULL,
`reset_key` varchar(32) NOT NULL,
`reset_key_expired` datetime NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `reset_key` (`reset_key`)

__‘user_provider’ Table__

This table is for manage social account. Many people have multiple social account. To protect duplication of user creation, social login information will be saved to ‘user_provider’ table and connect with main ‘user’ table.

CREATE TABLE `user_provider` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`provider` varchar(20) NOT NULL,
`provider_uid` varchar(255) NOT NULL,
KEY `user_id` (`user_id`)

3. Conclusion

In this article, we made a business flow of user management feature and also made a database.

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *

Let's connect and create
something awesome together!
2023 - Copyright, All Rights Reserved, Made by ZEMNA.NET with ❤️
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram