MySQL中Customer表的用户凭证存储方案咨询：单表存储还是拆分存储？

Great question—this is a super common design choice when building user-facing systems, and while there’s no absolute "right" answer, let’s walk through the tradeoffs based on your goals and future needs.

Option 1: Store Credentials Directly in the Customer Table #

This works best if you’re building a simple, minimal system (like a small internal tool or early prototype) where:

• User credentials rarely change (if ever)
• You don’t need to track audit history for password/username updates
• You don’t anticipate needing multiple credentials per user (e.g., no support for multiple login usernames or third-party auth down the line)

Even if you go this route, never store plaintext passwords—always hash them with a strong algorithm like Argon2 or bcrypt (avoid MD5/SHA-1 at all costs). The upside here is fewer database joins and a simpler schema to get started with.

Option 2: Use a Separate Table (e.g., UserCredentials) #

Based on the points you mentioned, this is the more robust and future-proof choice—especially if you’re building a system that needs to scale or prioritize security. Here’s why it makes sense:

• Enhanced security: You can restrict access to the credentials table to only the authentication service/roles that need it, while allowing broader access to the Customer table for other business logic. This reduces the risk of sensitive credential data being exposed accidentally.
• Audit & change tracking: You can easily add fields like updated_at, last_updated_by, or even link to a separate CredentialHistory table to track every password/username change. This is critical for compliance or debugging login issues.
• Avoid redundancy & schema bloat: If you ever need to support multiple credentials per user (e.g., temporary login tokens, OAuth provider accounts), a separate table lets you add those without cluttering up the Customer table with irrelevant fields. It also means queries that only need customer profile data don’t have to load sensitive credential fields.
• Better scalability: As your system grows, separating concerns keeps your schema clean. For example, you could later split authentication logic into its own service that only interacts with the UserCredentials table, without touching the customer profile data.

A Quick Schema Example for the Separate Table #

CREATE TABLE UserCredentials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customer(id) ON DELETE CASCADE
);

Final Recommendation #

If you’re investing time into building a system that needs to be secure, maintainable, and scalable long-term, go with the separate table. The minor extra complexity of joins is far outweighed by the security, auditability, and flexibility it provides. If you’re just testing a quick prototype, storing credentials in Customer is okay—but don’t skip password hashing!

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