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

Introduction: Multi‑Account Login

Internet applications often need to support login via multiple third‑party accounts such as NetEase, WeChat, and QQ. This unified approach is called multi‑account login.

1. Early‑Stage Solutions

Username/Password Registration and Login

When user volume is low, a self‑built authentication system is sufficient. The typical flow includes:

Frontend sends username and password to the server, which validates length, uniqueness, and encrypts the password (commonly using MD5) before storing it.

After validation, the credentials are saved to the database and any post‑registration actions (e.g., awarding points) are performed.

During login, the server checks login attempt thresholds (e.g., lockout after too many failures) and validates the credentials.

If the password is incorrect, the failure count is updated; exceeding the threshold locks the account temporarily using Redis expiration.

On successful login, subsequent business logic such as point accrual is executed.

Phone‑Number Registration and Login

SMS‑based registration is fast and user‑friendly. The flow is:

The user submits a phone number; the server stores it, generates a random verification code, and caches the pair in Redis with a typical 10‑minute TTL.

The user receives the SMS, enters the code, and the server verifies it against the cached value.

Upon successful verification, the login proceeds.

Although the process appears to lack an explicit registration step, submitting the phone number effectively registers the user, and the verification code acts as the login credential.

Q: What about passwords?

A: A later feature can allow users to bind a password to their phone number, but many modern mobile apps rely solely on phone verification.

2. Database Design – Core Tables

The following tables support both the username/password and phone‑number schemes:

Explanation: the structure stores all necessary fields for the two authentication methods.

3. Third‑Party Account Integration

Using QQ‑SDK as an example, the sequence diagram illustrates the flow:

Implementation steps:

The client launches the third‑party login UI, the user enters third‑party credentials, and receives access_token, openid, and expire_in (OAuth2.0 flow handled by the SDK).

The client sends access_token, openid, and login_type (e.g., qq, wechat) to the application server.

The server validates the token and openid with the corresponding third‑party user center.

If validation succeeds, the server checks whether a local record for the login_type and openid exists. If not, it fetches basic user info (username, avatar) and creates a local entry, returning a code value.

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’s expiration is extended on each request to achieve a “never‑expire” experience.

4. Detailed Database Schema for Multi‑Account System

Key tables:

users : Core business‑side user table, used for internal OAuth2.0 operations.

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

user_third_auth : Records third‑party account data.

user_auth_rel : Associates users with user_local_auth and user_third_auth.

Schema diagrams:

Explanation: the design separates self‑built users from third‑party accounts, allowing gradual migration from a purely internal system to a hybrid one as the product scales.

5. Conclusion

Integrating third‑party authentication is technically straightforward; adding a dedicated user_third_auth table enables support for many providers, though in practice only a few are needed to balance maintenance cost and UI complexity. The presented design is a simple, non‑sharded, non‑service‑oriented baseline that can be extended as user volume grows.