MySQL中锁定账户的处理方案咨询（教会执事系统场景）

Hey there! Let's dive into your account locking question and share some practical, database-design best-practice advice tailored to your church deacon web app.

1. Is a separate locked table necessary? #

Short answer: Probably not, unless you need to track extremely granular lock history. Here's why:

• A user's account status (active/locked/inactive) is a core attribute of the user entity itself. Storing this directly in your users table aligns with 2NF/3NF rules, as the status depends entirely on the user's primary key.
• A separate locked table creates an unnecessary 1:1 relationship, which adds complexity to queries (you'd have to join tables every time you check login eligibility) and doesn't gain you much unless you need to log every single lock/unlock event with details.

2. Recommended Field Design #

Option 1: Basic Status Fields (Simple Use Case) #

Add these columns to your existing users table to cover all your needs:

ALTER TABLE users
ADD COLUMN account_status ENUM('ACTIVE', 'LOCKED', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
ADD COLUMN failed_login_attempts INT NOT NULL DEFAULT 0,
ADD COLUMN lock_expiry_time DATETIME NULL;

• account_status:
  • ACTIVE: Current deacons who can log in and edit their personal info
  • LOCKED: Temporarily locked due to too many failed login attempts
  • INACTIVE: Historical users (retained for reporting) or manually deactivated accounts
• failed_login_attempts: Tracks consecutive failed logins; reset to 0 on successful login
• lock_expiry_time: For temporary locks (e.g., 15-minute lock after 5 failed attempts). When the current time exceeds this value, automatically reset the user's status to ACTIVE and clear this field.

Option 2: Add a Status History Table (For Auditing/Reporting) #

If you need to track why and when a user's status changed (e.g., "Admin locked account on 2024-05-20 due to inactivity" or "System locked after 5 failed logins"), add a separate audit table:

CREATE TABLE user_status_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    old_status ENUM('ACTIVE', 'LOCKED', 'INACTIVE') NOT NULL,
    new_status ENUM('ACTIVE', 'LOCKED', 'INACTIVE') NOT NULL,
    change_reason VARCHAR(255) NULL,
    changed_by INT NULL, -- ID of the user who made the change (NULL = system auto-action)
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (changed_by) REFERENCES users(id)
);

This table keeps a full history of status changes, which is perfect for compliance or reporting, while your users table only stores the current status for efficient login checks.

3. Aligning with Your Use Cases #

• Current Deacons Login: When a user tries to log in, first check if account_status is ACTIVE. If it's LOCKED, verify if lock_expiry_time has passed—if so, auto-reset their status to ACTIVE before allowing login.
• Admin/Supervisor Actions: When an admin deactivates a user or unlocks a locked account, update the users.account_status and add a record to user_status_changes (if you're using that table) to document the action.
• Retaining Historical Users: Instead of deleting old users, set their account_status to INACTIVE. You can add an additional term_end_date column to users to filter historical users for reporting (e.g., "show all inactive users whose term ended in the last 5 years").
• Login Failure Lock Logic: On each failed login, increment failed_login_attempts. When it hits your threshold (e.g., 5), set account_status to LOCKED and lock_expiry_time to CURRENT_TIMESTAMP + INTERVAL 15 MINUTE.

4. Bonus: Other Normalization & Model Tips #

Since you're brushing up on normalization, here are a few tweaks to make your model more robust:

• Role-Based Access: Instead of adding columns like is_supervisor or is_admin to users, use a role table for flexibility:

  CREATE TABLE roles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      role_name VARCHAR(50) NOT NULL UNIQUE -- e.g., 'ADMIN', 'SUPERVISOR', 'DEACON'
  );
  
  CREATE TABLE user_roles (
      user_id INT NOT NULL,
      role_id INT NOT NULL,
      PRIMARY KEY (user_id, role_id),
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (role_id) REFERENCES roles(id)
  );
  

  This lets you assign multiple roles to a user (e.g., a supervisor who's also an admin) without modifying the users table structure.
• Duty Schedules: If deacons have recurring or multiple duty shifts, create a shifts table and a user_shifts join table instead of storing shift data directly in users. This follows the 1:many relationship rule (one user can have many shifts).
• Ordination Records: For ordination details, a separate ordination_records table makes sense—deacons might be ordained multiple times (e.g., promoted to a higher role), so this table can track each ordination date, position, and related details.

Final Thoughts #

A standalone locked table isn't the most efficient solution here. Instead, embedding status fields in the users table (plus an optional audit table for history) keeps your model clean, compliant with normalization rules, and easy to query. This approach covers all your use cases: active logins, temporary locks, historical user retention, and admin controls.

内容的提问来源于stack exchange，提问作者suffa