Multi-Identifier Unified Login & Social Binding: Building a Scalable Authentication System

🌐 Language: English Version | 中文版

As products evolve, login options multiply: username, email, phone number, plus Google, GitHub, WeChat, and enterprise SSO. Traditional “wide table” user designs face a dilemma: constantly adding fields leads to bloated schemas, or limiting login methods sacrifices user experience. This article explores a domain model that separates AuthIdentity from User to build an extensible, unified authentication system.

TL;DR

• Separate concerns: Split “who the user is” (User) from “how to prove it” (AuthIdentity)
• Zero schema changes: Adding new login methods only requires INSERT, not ALTER TABLE
• Flexible progression: Support “passwordless” users who add credentials over time
• Natural conflict prevention: Composite unique index (identity_type, identifier) prevents duplicate bindings

---

1. From “Wide Table” to “Separation Model”

1.1 The Pain of Traditional Design

Early user tables typically looked like this:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    mobile VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255),
    google_id VARCHAR(100),
    wechat_openid VARCHAR(100),
    github_id VARCHAR(100),
    -- Add a new column for every login method
    created_at TIMESTAMP
);

The problems are obvious:

• Poor extensibility: Adding login methods requires schema changes—risky for large production tables
• Tight coupling: Password field is mandatory for all users, blocking “SMS-only” or “social-only” scenarios
• Chaotic uniqueness: username, email, mobile each have unique constraints, but social IDs scatter across different fields
• Data redundancy: Passwordless users have empty password_hash; users without email have empty email

1.2 Core Idea of the Separation Model

Completely decouple “who the user is” (User) from “how to prove you’re you” (AuthIdentity):

• User: Only cares about basic user info, not login methods
• AuthIdentity: Only cares about authentication credentials, not other user data
• UserPassword: Password as an independent entity, supporting “passwordless user” scenarios

The immediate benefit: Adding a new login method only requires inserting one AuthIdentity record—no schema changes needed.

---

2. Domain Model Design

2.1 Core Entity Relationships

classDiagram
    class User {
        +Long id
        +String nickname
        +String avatar
        +String status
        +datetime created_at
    }

    class AuthIdentity {
        +Long id
        +Long user_id
        +String identity_type
        +String identifier
        +datetime verified_at
    }

    class UserPassword {
        +Long user_id
        +String password_hash
    }

    User "1" --> "n" AuthIdentity : has
    User "1" --> "0..1" UserPassword : has

2.2 Entity Responsibility Boundaries

User: The User Aggregate Root

The User entity stores only business-agnostic basic information:

public class User {
    private Long id;
    private String nickname;
    private String avatar;
    private UserStatus status; // ACTIVE, SUSPENDED, etc.
    private LocalDateTime createdAt;
    // Note: no password, email, or mobile fields
}

Design Intent: The User table should be stable. Regardless of how many login methods are supported later, this table never needs changes. It answers “does this user exist?” not “how does this user log in?”

AuthIdentity: Authentication Identity

AuthIdentity records all login credentials a user possesses—one record per login method:

public class AuthIdentity {
    private Long id;
    private Long userId;          // Links to User
    private String identityType;  // "email", "mobile", "username", "google", "wechat"
    private String identifier;    // The actual value: email address, phone, username, OpenID
    private LocalDateTime verifiedAt;     // Verification time (for email/phone)
}

Key Design Points:

• Composite unique index: (identity_type, identifier) ensures one email can’t bind to two accounts
• Flexible identifier: 13800138000 can be a mobile-type identifier or a username-type identifier—no conflict
• Minimal core fields: AuthIdentity stores only the minimum info needed to locate users; extended data can be stored separately

UserPassword: Independent Password Entity

public class UserPassword {
    private Long userId;          // One-to-one with User
    private String passwordHash;  // Argon2id hash
    private LocalDateTime updatedAt;
}

Why a separate table?

1. Support passwordless users: Users with only SMS or social login have no UserPassword record
2. Independent password policy: Can apply stronger security controls (stricter access control, audit logs)
3. Password history: If you need “can’t reuse last 5 passwords” policy, can extend to one-to-many relationship

---

3. Model in Action

3.1 Scenario 1: User Registration

Email registration:

-- 1. Create User
INSERT INTO users (id, nickname, status, created_at) 
VALUES (1, 'John Doe', 'ACTIVE', NOW());

-- 2. Create AuthIdentity (email)
INSERT INTO auth_identities (user_id, identity_type, identifier, verified_at)
VALUES (1, 'email', 'john@example.com', NOW());

-- 3. Create UserPassword
INSERT INTO user_passwords (user_id, password_hash)
VALUES (1, '$argon2id$v=19$m=65536,t=3,p=4$...');

WeChat one-click registration (passwordless user):

-- 1. Create User
INSERT INTO users (id, nickname, avatar, status, created_at) 
VALUES (2, 'WeChat User', 'https://wx.qq.com/avatar.jpg', 'ACTIVE', NOW());

-- 2. Create AuthIdentity (wechat)
INSERT INTO auth_identities (user_id, identity_type, identifier)
VALUES (2, 'wechat', 'oABCD1234567890');

-- Note: no user_passwords record

3.2 Scenario 2: Adding Login Methods

User originally logs in with email, now wants to bind mobile:

-- Just insert one record—no changes to users table
INSERT INTO auth_identities (user_id, identity_type, identifier, verified_at)
VALUES (1, 'mobile', '+1234567890', NOW());

User wants to also bind GitHub:

INSERT INTO auth_identities (user_id, identity_type, identifier)
VALUES (1, 'github', '12345678');

Feel the extensibility: Each new login method is just one INSERT. In system code, it’s just adding one enum value for identity_type.

3.3 Scenario 3: Login Flow Collaboration

User enters john@example.com + password to log in:

public LoginResult login(String input, String password) {
    // 1. Find AuthIdentity
    AuthIdentity identity = authIdentityRepo
        .findByTypeAndIdentifier("email", input)
        .orElseThrow(() -> new IdentityNotFoundException());
    
    // 2. Find associated User
    User user = userRepo.findById(identity.getUserId())
        .orElseThrow(() -> new UserNotFoundException());
    
    // 3. Verify password (from independent UserPassword table)
    UserPassword userPwd = userPasswordRepo.findByUserId(user.getId())
        .orElseThrow(() -> new PasswordNotSetException());
    
    if (!passwordEncoder.matches(password, userPwd.getPasswordHash())) {
        throw new InvalidPasswordException();
    }
    
    // 4. Issue token
    return issueToken(user);
}

Note the query path: AuthIdentity → User → UserPassword. Each entity has its own responsibility.

3.4 Scenario 4: Social Account Binding Conflict

User A is logged in, tries to bind a WeChat already bound to User B:

public void bindSocialAccount(Long currentUserId, String wechatOpenid) {
    // 1. Check if this WeChat is already bound
    Optional<AuthIdentity> existing = authIdentityRepo
        .findByTypeAndIdentifier("wechat", wechatOpenid);
    
    if (existing.isPresent()) {
        Long boundUserId = existing.get().getUserId();
        if (!boundUserId.equals(currentUserId)) {
            // Already bound to another user—reject
            throw new IdentityAlreadyBoundException(
                "This WeChat is already bound to user " + boundUserId);
        }
        // Already bound to current user—idempotent return
        return;
    }
    
    // 2. Create new AuthIdentity
    AuthIdentity newIdentity = new AuthIdentity();
    newIdentity.setUserId(currentUserId);
    newIdentity.setIdentityType("wechat");
    newIdentity.setIdentifier(wechatOpenid);
    authIdentityRepo.save(newIdentity);
}

Key Point: The (identity_type, identifier) unique constraint naturally prevents duplicate bindings. Conflict handling just needs simple logic.

---

4. Architectural Advantages

4.1 Extensibility: Zero Schema Changes for New Login Methods

Compare with traditional design:

Approach	Cost of Adding Login Method
Wide Table	ALTER TABLE to add column—high risk for large tables, requires gradual rollout
Separation Model	INSERT one record, add enum value in code—pure business logic change

4.2 Flexibility: Support “Progressive” Authentication

Users can start with a “passwordless” account (social login only), then add password and bind mobile later:

-- Initial state: only WeChat login
SELECT * FROM auth_identities WHERE user_id = 1;
-- user_id | identity_type | identifier
-- 1       | wechat        | oABCD123...

-- Later bind mobile
INSERT INTO auth_identities ... ('mobile', '+1234567890');

-- Later set password
INSERT INTO user_passwords ... (1, 'password_hash');

-- Final state: three login options
SELECT identity_type, identifier FROM auth_identities WHERE user_id = 1;
-- wechat | oABCD123...
-- mobile | +1234567890

4.3 Data Consistency: Unbinding Doesn’t Lose User Data

Unbinding mobile just deletes one AuthIdentity record:

DELETE FROM auth_identities 
WHERE user_id = 1 AND identity_type = 'mobile' AND identifier = '+1234567890';

The User entity is unaffected—user’s orders, posts, comments remain. As long as at least one AuthIdentity remains, the user can still log in.

---

5. Implementation Notes

5.1 Index Design

-- Core query: find user by login method
CREATE UNIQUE INDEX idx_auth_identity_type_identifier 
ON auth_identities(identity_type, identifier);

-- Admin scenario: view all user's login methods
CREATE INDEX idx_auth_identity_user_id 
ON auth_identities(user_id);

-- Password query
CREATE UNIQUE INDEX idx_user_password_user_id 
ON user_passwords(user_id);

5.2 Transaction Boundaries

Creating a user requires consistency across three tables:

@Transactional
public User createUserWithEmail(String email, String password) {
    // 1. Create User
    User user = userRepo.save(new User());
    
    // 2. Create AuthIdentity
    authIdentityRepo.save(new AuthIdentity(user.getId(), "email", email));
    
    // 3. Create UserPassword
    userPasswordRepo.save(new UserPassword(user.getId(), hash(password)));
    
    return user;
}

5.3 Security Considerations

• Verification codes: Used to verify phone/email ownership; mark verified_at after verification
• Password policy: Use Argon2id, recommended parameters m=65536, t=3, p=4
• Token storage: Social account AccessTokens should be stored separately and encrypted
• Brute force protection: Rate limit by identifier + identity_type dimension, not just IP

---

6. Comparison: When to Use This Model

Scenario	Wide Table	Separation Model
Only username + password	✅ Simple	Overkill
Username + email + mobile	⚠️ Manageable	✅ Better
Multiple social logins + SSO	❌ Painful	✅ Essential
Need passwordless option	❌ Hard	✅ Natural
Frequent login method changes	❌ Schema migrations	✅ Just INSERT

---

7. Key Takeaways

The core value of separating AuthIdentity from User: decouple “identity” from “authentication method”.

This design enables systems to:

1. Scale flexibly: Add login methods with just records, no schema changes
2. Evolve progressively: Support users moving from “social login” to “multi-factor authentication”
3. Protect data: Unbinding login methods doesn’t lose user business data
4. Maintain clarity: Each entity has single responsibility, code is easier to maintain

When your product evolves from “username/password only” to “supporting phone, email, WeChat, GitHub, enterprise SSO”, this separation model saves you from painful schema migrations and makes authentication a truly evolvable business capability.

---

Related Resources

• OWASP Authentication Cheat Sheet
• NIST Digital Identity Guidelines
• Argon2 Password Hashing