Designing Scalable Multi-Account Login: Strategies, Flows, and Database Schemas

Introduction: Multi-Account Login

In internet applications, we often allow users to log in with multiple third‑party accounts such as NetEase, WeChat, QQ, which we refer to as multi‑account unified login.

This article explains the technical solution details and corresponding table designs and process flows. It does not provide concrete code, but offers a solid design mindset.

1. Early Stage (Startup Phase)

At the early stage, user volume is small, and third‑party accounts may not yet be integrated, so a self‑built authentication system is sufficient. Common solutions include:

Username & Password Registration/Login

This method is common for early websites: users register then log in. The flow diagram is shown below.

Process description:

Frontend sends username and password to server; server validates length, uniqueness, etc. Password should be encrypted (e.g., MD5) before transmission and stored with an additional encryption layer; never store plaintext passwords.

After validation, the server writes the credentials to the database and performs subsequent operations such as awarding points.

During login, frontend sends username and password; server first checks if login attempts exceed a threshold, otherwise proceeds.

If credentials are incorrect, the system increments the failure count; exceeding the limit locks the account (a “blackroom”) with an expiration set via Redis.

On successful login, further post‑login logic such as point accrual is executed.

Phone Number Registration/Login

SMS‑based registration is mature and convenient. The flow is as follows:

Process description:

User enters phone number; server stores it, generates a random verification code, binds the code and phone number in Redis with a typical 10‑minute expiration.

User receives the SMS, inputs the code; server verifies the code from Redis and returns an error if mismatched.

Upon successful verification, the user is considered logged in. The initial phone number submission acts as registration, and the code entry serves as login.

Q: What about passwords?

A: A later feature can allow users to set a password for the phone number, but in many mobile‑first scenarios passwords are optional.

2. Database Design

Table Structure

The tables listed can satisfy both the username/password and phone‑based schemes.

3. Integrating Third‑Party Accounts

Using QQ‑SDK as an example, the sequence diagram is shown below:

Implementation Idea:

Client initiates third‑party login UI, user enters third‑party credentials; upon success the provider returns access_token, openid, and expiration (OAuth2.0 flow handled by the SDK).

Client sends access_token, openid, and login_type (e.g., qq, wechat) to the application server, which validates them against the corresponding user center.

Validation: If validation fails, an error code is returned.

On success, the server checks whether a local record for the given login_type and openid exists; if not, it fetches basic user info (username, avatar) from the provider and creates a local entry, returning a code.

If the record exists, the server proceeds with login and returns a code.

The client exchanges the code for a token according to OAuth2.0; the token has a long lifespan and its expiration is extended on each request to achieve “never‑offline” behavior.

4. Detailed Database Schema

Key tables:

users : Core business‑side user table, handling internal OAuth2.0.

user_local_auth : Stores username/password and phone‑based login information.

user_third_auth : Records third‑party user data.

user_auth_rel : Relates the users table with user_local_auth and user_third_auth.

These tables separate self‑built users from third‑party accounts, reflecting a typical evolution from a purely internal system to one that integrates external providers.

5. Summary

Integrating third‑party authentication is technically straightforward; adding a dedicated user_third_auth table supports many providers, though in practice only a few are needed to keep maintenance and UI manageable. This design is a simple, non‑sharded, non‑service‑oriented solution that can be extended as user volume grows.

By studying the above, readers should gain a solid understanding of multi‑account login architecture, while recognizing that additional considerations such as sharding, service decomposition, and scaling are required for larger systems.