React / 系统课时

关系数据库与数据建模

关系数据库与数据建模

本课把“用户管理项目、项目包含任务”的需求转换为可执行的关系模型。完成后,你将得到三张职责清晰的表,并能用主键、外键和约束保证数据始终符合业务规则。

学习目标

  • 从业务描述中识别实体、属性与关系
  • 为用户、项目和任务设计主键、字段及约束
  • 使用 PostgreSQL 建表并验证关联查询结果

从业务对象识别实体

先找能够独立存在、需要长期保存且拥有自身属性的对象。本例包含用户、项目和任务三个实体:用户有邮箱和显示名称,项目有名称与负责人,任务有标题、状态和截止日期。

不要把所有信息塞进一张表。若任务表重复保存用户邮箱和项目名称,修改名称时就要更新多行,还可能产生同一项目出现多个名称的异常。关系模型应让每项事实只保存在最合适的位置。

设计主键、字段与约束

每张表都使用无业务含义的自增 id 作为主键,避免邮箱、项目名称等可变字段参与关联。业务唯一性由独立约束表达,例如用户邮箱全局唯一,同一用户下的项目名称不能重复。

字段约束应尽量下沉到数据库:必填字段使用 NOT NULL,有限状态使用 CHECK,默认状态使用 DEFAULT。这样即使未来出现脚本、后台任务或其他服务写库,错误数据也会被数据库拒绝。

建立实体关系

用户、项目与任务实体关系图

一个用户可以拥有多个项目,但每个项目只有一个负责人,因此 project.owner_id 指向 app_user.id。一个项目可以包含多个任务,但每个任务只属于一个项目,因此 task.project_id 指向 project.id。

外键不仅记录关联,还决定删除行为。本例禁止直接删除仍拥有项目的用户,避免整批业务数据被误删;删除项目时级联删除其任务,因为脱离项目的任务已失去业务意义。

用 PostgreSQL 落地模型

下面的 SQL 会重建三张表,插入一组测试数据,并查询用户、项目和任务的完整关系。请在独立练习数据库中执行,DROP TABLE 会删除同名测试表。

sql
DROP TABLE IF EXISTS task, project, app_user CASCADE;

CREATE TABLE app_user (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(80) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE project (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id BIGINT NOT NULL
        REFERENCES app_user(id) ON DELETE RESTRICT,
    name VARCHAR(120) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (owner_id, name)
);

CREATE TABLE task (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id BIGINT NOT NULL
        REFERENCES project(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'TODO'
        CHECK (status IN ('TODO', 'DOING', 'DONE')),
    due_date DATE
);

INSERT INTO app_user (email, display_name)
VALUES ('demo@example.com', '演示用户');

INSERT INTO project (owner_id, name)
VALUES (1, 'React 全栈任务板');

INSERT INTO task (project_id, title, status)
VALUES (1, '完成数据库建模', 'DOING');

SELECT
    u.email AS user_email,
    p.name AS project_name,
    t.title AS task_title,
    t.status
FROM app_user AS u
JOIN project AS p ON p.owner_id = u.id
JOIN task AS t ON t.project_id = p.id;

预期输出:

text
    user_email    |    project_name    |    task_title     | status
------------------+--------------------+-------------------+--------
 demo@example.com | React 全栈任务板   | 完成数据库建模    | DOING
(1 row)

若插入重复邮箱、未知项目的任务或不在允许集合中的状态,数据库会直接报约束错误。这说明完整性规则已经成为模型的一部分,而不是只依赖前端校验。

检查模型边界

模型设计完成后,应反向验证几类操作:重复数据是否会被拒绝,父记录不存在时能否插入子记录,删除父记录时子记录如何处理,以及常用查询是否能通过外键列连接。

当数据量增长后,应为高频筛选和连接列补充索引。PostgreSQL 不会因为创建外键就自动为引用列建立普通索引,因此实际项目通常还会为 project.owner_id 和 task.project_id 创建索引。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

关系数据库与数据建模

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通