优化数据库设计：在统一视图中安全管理多状态记录的删除操作

本文探讨了在统一视图中展示来自多个具有相同主键但代表不同状态（如待审批和已审批）的数据时，如何安全地识别并删除特定记录的问题。针对客户端识别的安全性缺陷，文章提出了核心的数据库设计优化方案：将多表合并为单表并引入“状态”列，或采用独立的“记录状态”表。通过这些服务端驱动的解决方案，确保了数据操作的准确性、安全性和可维护性。

挑战：统一视图下多源数据的删除困境

在现代应用开发中，将来自不同数据源或代表不同状态（例如“待审批”和“已审批”）的数据整合到单一用户界面视图中是常见的需求。然而，当这些数据表拥有相同的结构甚至重叠的主键ID时，执行数据操作（尤其是删除）会带来显著的挑战。

考虑一个典型场景：系统中有两张表，table1 存储已审批（Approved）信息，table2 存储待审批（Pending）信息。两张表都使用 id 作为主键，并且可能存在相同的 id 值，但它们代表的是不同状态下的独立记录。当用户在一个统一的视图中看到这些来自两张表的数据并尝试删除某条记录时，后端如何准确判断应该从 table1 还是 table2 中删除记录，成为了一个关键问题。

例如： table1 (已审批信息) | id | name | description | creator | |----|-------|-------------|---------| | 10 | test1 | N/A | 100 | | 11 | test2 | N/A | 100 |

table2 (待审批信息) | id | name | description | creator | |----|-------|-------------|---------| | 10 | test1 | N/A | 105 | | 12 | test3 | N/A | 106 |

如果用户希望删除 id 为 10 的待审批记录，直接使用 id=10 进行删除操作将无法区分是 table1 中的记录还是 table2 中的记录。依赖客户端（如通过数据属性）传递信息来区分源表是极其不安全的，因为客户端数据容易被篡改，可能导致误删或安全漏洞。

数据库设计优化方案

解决这一问题的根本方法在于优化数据库设计，确保每条记录的唯一性及其状态信息在服务端得到明确标识和管理。

方案一：引入“状态”列（推荐）

最直接且推荐的解决方案是将原本分离的、代表不同状态的表合并为一张主表，并通过引入一个“状态”（status）列来区分记录的当前状态。这不仅简化了数据库结构，也极大地提升了数据管理的安全性和效率。

设计原则： 将所有相关信息整合到一张表中，并添加一个 status 列。该列可以存储字符串（如 'pending', 'approved', 'rejected'）或整数代码（如 1=pending, 2=approved, 3=rejected），后者在存储和查询效率上通常更优。

示例表结构：

CREATE TABLE records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    creator INT,
    status VARCHAR(50) NOT NULL -- 可以是 'pending', 'approved', 'rejected' 等
);

或者使用整数代码以优化存储：

CREATE TABLE records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    creator INT,
    status TINYINT NOT NULL -- 1=pending, 2=approved, 3=rejected
);

数据示例：

畅图

AI可视化工具

179

查看详情

删除操作： 当用户请求删除某条记录时，前端只需传递该记录的唯一 id。后端接收到 id 后，可以根据业务逻辑，结合 status 列进行精确删除。例如，如果用户请求删除 id 为 16 的待审批记录，后端执行如下SQL：

DELETE FROM records
WHERE id = 16 AND status = 'pending';

或者使用状态码：

DELETE FROM records
WHERE id = 16 AND status = 1; -- 假设 1 代表 'pending'

这种方式确保了删除操作的原子性和准确性，因为 id 和 status 共同唯一标识了要删除的记录，且 status 信息完全由服务端管理。

方案二：独立的记录状态表

如果业务逻辑要求主记录表保持纯净，或者状态信息本身非常复杂、需要额外的属性，可以考虑将状态信息存储在独立的表中，并通过外键关联主记录表。

设计原则： 保留一个主记录表，例如 record_main，它包含所有核心信息。然后创建一个 record_status 表，包含 record_id（作为外键关联 record_main）和 status 列。

示例表结构：

CREATE TABLE record_main (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    creator INT
);

CREATE TABLE record_status (
    record_id INT PRIMARY KEY, -- 外键，指向 record_main.id
    status VARCHAR(50) NOT NULL,
    FOREIGN KEY (record_id) REFERENCES record_main(id) ON DELETE CASCADE
);

删除操作： 当用户请求删除某条记录时，前端同样只需传递 record_main 表中的 id。后端接收 id 后，可以通过 JOIN 操作或子查询来确认记录的状态并执行删除。

-- 假设要删除 id 为 12 的待审批记录
DELETE FROM record_main
WHERE id = (
    SELECT rm.id
    FROM record_main rm
    JOIN record_status rs ON rm.id = rs.record_id
    WHERE rm.id = 12 AND rs.status = 'pending'
);

-- 或者，如果 record_status 表的外键设置了 ON DELETE CASCADE
-- 只需要删除 record_status 表中的对应记录，record_main 中的记录会自动删除
DELETE FROM record_status
WHERE record_id = 12 AND status = 'pending';

这种方法在某些场景下提供了更大的灵活性，例如可以为 record_status 表添加时间戳来追踪状态变更历史，但相比方案一，查询和删除操作会略微复杂。

实现细节与注意事项

1. 服务端严格验证： 无论采用哪种方案，核心原则是所有删除请求必须在服务器端进行严格验证。客户端只负责传递待删除记录的唯一标识符（如 id），而记录的“状态”信息应由服务器根据数据库查询结果来判断，绝不能信任客户端传递的状态信息。

2. API设计： 前端在发起删除请求时，只需提供一个 recordId 参数即可。后端API根据 recordId 查询数据库，获取该记录的完整信息（包括状态），然后根据业务逻辑决定是否允许删除以及如何删除。

   // 前端调用示例
   axios.delete('/api/records/delete', { params: { recordId: 16 } });

   登录后复制

   // 后端Spring Boot Controller 示例
   import org.springframework.http.HttpStatus;
   import org.springframework.http.ResponseEntity;
   import org.springframework.web.bind.annotation.*;
   
   // 假设 Record 和 RecordService 是您的数据模型和服务层
   class Record {
       private Long id;
       private String name;
       private String status; // 对应数据库的 status 列
       // ... 其他字段和getter/setter
       public Long getId() { return id; }
       public void setId(Long id) { this.id = id; }
       public String getName() { return name; }
       public void setName(String name) { this.name = name; }
       public String getStatus() { return status; }
       public void setStatus(String status) { this.status = status; }
   }
   
   interface

   登录后复制

以上就是优化数据库设计：在统一视图中安全管理多状态记录的删除操作的详细内容，更多请关注php中文网其它相关文章！