客户与地址表最优设计咨询：支持主副双地址场景

Great question! Let's start by breaking down your three existing options, then discuss if there's a more optimal approach for your "primary + secondary address" use case.

分析现有方案 #

方案1：Clients + Addresses with flag_primary

• Pros: Simple structure, keeps all address data in one table, and the flag_primary makes it clear which address is the main one.
• Cons: You’re missing a client_id foreign key in the Addresses table (I assume that’s an oversight!) to link addresses to their clients. Even with that, you need to add a partial unique constraint (like UNIQUE(client_id, flag_primary) WHERE flag_primary = TRUE) to prevent a client from having multiple primary addresses—without this, your data integrity is at risk.

方案2：Clients with primary_address_id + Addresses

• Pros: Explicitly links the primary address directly to the client, which makes querying the main address faster (no need to filter by a flag).
• Cons: Again, the Addresses table needs a client_id to track secondary addresses. You also have to handle edge cases: what if a client hasn’t added any addresses yet? You’ll need to allow primary_address_id to be NULL. Plus, if you delete the primary address, you have to update the Clients table to avoid orphaned foreign keys, which adds extra application logic or database triggers.

方案3：Clients + Addresses + User_Settings

• Pros: Separates the "address preference" (which is primary) from the address data itself, following the single responsibility principle.
• Cons: This is over-engineered for a simple "primary + secondary" use case. The User_Settings table adds unnecessary complexity—you could achieve the same result with a flag in the Addresses table or a direct foreign key in Clients, without an extra table.

推荐的最优方案（改进版方案1） #

For your specific need (tracking a primary and secondary address per client), I’d recommend a refined version of Scheme 1 that fixes its gaps while keeping things simple and scalable:

Table Structure:

• Clients
  • id (PK, auto-incrementing)
  • name (VARCHAR)
  • surname (VARCHAR)
• Addresses
  • id (PK, auto-incrementing)
  • client_id (FK to Clients.id, NOT NULL)
  • line1 (VARCHAR)
  • city (VARCHAR)
  • country (VARCHAR)
  • postcode (VARCHAR)
  • is_primary (BOOLEAN, DEFAULT FALSE)
  • Constraint: UNIQUE(client_id, is_primary) WHERE is_primary = TRUE (ensures only one primary address per client)

Why this works best:

1. Data Integrity: The partial unique constraint guarantees no client has more than one primary address—no extra application logic needed to enforce this.
2. Simplicity: No extra tables, no orphaned foreign key risks like in Scheme 2. Querying the primary address is straightforward (SELECT * FROM Addresses WHERE client_id = ? AND is_primary = TRUE).
3. Scalability: If you later need to add more than one secondary address (e.g., a billing address and a shipping address), you can easily extend this by adding a address_type column (ENUM('primary', 'secondary', 'billing')) instead of a boolean flag—no major schema changes required.
4. Normalization: Follows database normalization rules (no duplicated address fields, all address data lives in one place), making maintenance easier if you ever need to modify address fields (like adding line2 or state).

Is there an alternative for strict "only two addresses"? #

If you absolutely know you’ll never need more than two addresses per client, you could consider adding primary and secondary address fields directly to the Clients table—but this is strongly discouraged. It violates first normal form (duplicating address columns), makes schema updates a nightmare (e.g., if you need to add a state field, you have to add it twice), and limits flexibility entirely.

内容的提问来源于stack exchange，提问作者Michel Dambros Figueiredo