数据库规范化：审批状态表拆分后的最优设计方案咨询

Great question—balancing database normalization with query performance is one of the most common (and tricky) tradeoffs in schema design, especially for approval workflows. Let’s walk through your options and find the best fit:

1. Ditch Approval1/Approval2 Tables—Use a Single, Generic Approval Table #

First off, splitting into separate Approval1 and Approval2 tables isn’t the most efficient normalized approach. Those two tables represent the same type of data (approval records) — the only difference is the approval level. Instead, create a single Approval table that handles all approval tiers:

-- Request table (keeps core request data)
CREATE TABLE Request (
    requestid INT PRIMARY KEY,
    quantity INT,
    requestedby INT, -- Foreign key to your Users table
    requestdate DATE
);

-- Generic Approval table (handles all approval levels)
CREATE TABLE Approval (
    approval_id INT PRIMARY KEY AUTO_INCREMENT,
    requestid INT FOREIGN KEY REFERENCES Request(requestid),
    approver_role VARCHAR(20), -- e.g., 'level1', 'level2' to distinguish tiers
    approver_user_id INT, -- Foreign key to your Users table
    additional_info TEXT,
    date_approved DATE,
    status ENUM('denied', 'approved', 'pending') -- Add 'pending' if you need to track in-progress approvals
);

Why this works: #

• Full normalization: No redundant columns, and you can easily add more approval tiers later (e.g., level3) without modifying your schema.
• Better performance than two separate tables: With a composite index on (requestid, approver_role), joining Request and Approval will be lightning fast—databases are optimized for these types of joins.
• Cleaner queries: To get all approvals for a request, you just do a single LEFT JOIN instead of joining two separate tables.

2. Addressing Performance Concerns #

If you’re worried about join overhead, here’s how to mitigate it:

• Add proper indexes:
  • Request.requestid is already the primary key (so indexed by default).
  • Add a composite index on Approval(requestid, approver_role) — this lets the database quickly find all approval records for a specific request and tier.
• Avoid over-querying: Only fetch the columns you need instead of SELECT *. For example, if you just need the status of level1 and level2 approvals, use conditional aggregation to get a flat result set similar to your original table:

  SELECT 
      r.requestid,
      r.quantity,
      MAX(CASE WHEN a.approver_role = 'level1' THEN a.status END) AS approval1_status,
      MAX(CASE WHEN a.approver_role = 'level2' THEN a.status END) AS approval2_status
  FROM Request r
  LEFT JOIN Approval a ON r.requestid = a.requestid
  GROUP BY r.requestid;
  

3. When to Consider Denormalization (Your Original Table) #

The only time I’d recommend sticking with your original denormalized table is if:

• Your approval workflow is guaranteed to never change (only two tiers, forever).
• Query performance is the absolute top priority, and you’re willing to accept the maintenance tradeoffs.

But be warned: Denormalization makes your schema rigid. If the business ever needs a third approver, you’ll have to add 4+ new columns (approver3, approval3_status, etc.) and update all your queries, inserts, and updates to handle them. It’s a short-term win that can lead to long-term technical debt.

4. Middle Ground: Hybrid Approach #

If you want the best of both worlds, you can add a few redundant columns to the Request table for quick access to commonly used approval data (e.g., overall_approval_status, level1_approval_status), and keep the full approval history in the Approval table.

• Use database triggers or your application logic to update these redundant columns whenever an approval is added/changed.
• This lets you query the core request status without joining, while still maintaining a normalized record of all approval details for auditing or historical purposes.

Final Recommendation #

Go with the generic Approval table approach. With proper indexing, the performance impact of joins will be negligible, and you’ll get a flexible, maintainable schema that can adapt to future changes. Denormalization should only be a last resort if you’ve proven that joins are causing measurable performance issues (which is rare with modern databases).

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