门诊系统数据库设计：从E-R模型到表结构落地实践

简介：一份面向软件工程、数据库课程设计学生的医院门诊管理系统数据库设计完整文档。内容以结构化分析方法为主线，系统覆盖需求分析、数据流程图、数据字典、分E-R图与全局E-R图、逻辑设计、物理设计以及SQL Server实施与测试等关键环节，可帮助读者掌握小型信息管理系统数据库从业务建模到落地的全流程。压缩包内含1个doc文件，共731KB，文档章节清晰，包含病人信息、医生信息、药品信息、诊断信息等核心表结构设计，并附有关系模式规范化处理与数据库对象建立细节。已有2831人学习浏览，适用于课程设计参考、数据库设计方法入门或毕业设计前期方案整理。通过该文档可快速理清医院门诊业务中的数据流与处理逻辑，借鉴其数据字典与E-R图设计思路，还可对照关系模式定义及SQL Server实现步骤完善自身项目方案。

1. 门诊数据库设计，先卡住的是数据模型

接手或重写一个医院门诊管理系统，最先暴露问题的往往不是前端页面，也不是接口性能，而是数据库表结构跑不顺业务。挂一个号要翻三张表，统计一个科室的日门诊量要 join 六次，退号时处方和费用状态对不上——这类问题本质上是概念模型阶段就埋下的雷。门诊系统的数据链路并不复杂，核心就一条主线：患者来院、挂号分诊、医生接诊、开立处方、缴费取药。每一步都会产生独立的数据实体，而且这些实体之间存在强时序依赖。课程设计里做这套数据库，常见做法是先把 E-R 图画清楚，再转成关系模型，最后用 DDL 落地。对第一次完整走数据库设计流程的人来说，难点不在 SQL 语法，而在识别业务域、确定实体基数、设计状态流转。这篇从需求建模开始，一直到 PowerDesigner 画图落地，按可复现的路径把整套设计过程讲完整。

2. 门诊业务建模：把流程转成实体关系图

2.1 用业务流程找实体，而不是照着界面抄

很多人做课程设计时习惯打开某个开源项目的界面截图，照着页面上有哪些输入框就来建表。挂号页面有患者姓名、科室、医生、号别，于是就把这些字段塞进一张挂号表里。这样做出来的表结构在写 demo 时能用，但一旦要统计「某医生上月门诊收入」「某科室药品占比」这类报表，数据就会粘成一团，拆都拆不开。

正确顺序应该是先梳理门诊流程中每个动作产生什么数据。患者首次来院先要建档案，产生患者主索引；挂号动作记录挂号的科室、医生、号别、费用；医生接诊后开立处方，处方拆成主表和明细表；收费动作把处方内容和缴费状态关联起来。一个流程节点对应一组实体，实体之间的连线就是后续外键关系的来源。

门诊系统的核心实体梳理下来，至少有七张基础表：患者、科室、医生、挂号、处方主表、处方明细、药品。再往外扩展还有收费记录、检查检验申请、退号退费记录。课程设计通常做到药品和处方这一层就足够体现建模能力，不必强行塞进住院、体检等无关模块。

2.2 实体基数和关系方向的判断

实体关系是概念模型阶段最容易画错的部分。以患者和挂号为例，一个患者可以多次就诊，每次就诊生成一条挂号记录，所以患者对挂号是一对多。挂号单对应一次就诊过程，医生在这张挂号单下开处方，所以挂号对处方是一对一或一对多——实际业务中一张挂号单可能开多张处方，但简化模型里做成一对一更利于理解主键传递关系。

用表格把主要实体关系定下来：

基数确定之后，外键的指向就清楚了。「多」的那一端保存「一」的那一端的主键。挂号表里同时出现患者ID和医生ID，处方明细表里同时出现处方ID和药品ID，都是这个规则的直接应用。

2.3 状态字段是门诊系统的隐藏需求

实体和基数只是静态骨架，门诊系统真正复杂的是状态流转。挂号的完整生命周期是：待接诊、接诊中、已完成、已退号。处方也有状态：已开立、已缴费、已取药、已退费。这些状态如果不在表结构阶段预留字段，后面做业务逻辑时只能在代码里维护内存状态，数据库完全没有约束能力。

设计上建议每个核心表都带上状态字段，并配合创建时间和更新时间。挂号表的 status 用 TINYINT 存，配合注释区分取值范围，比直接用字符串更省空间，也方便做索引。如果课程设计的评分点里包含数据完整性，可以在状态字段上增加 CHECK 约束。MySQL 8.0.16 之后 CHECK 约束会真正生效，之前版本只是语法兼容，要注意这一点。

3. 表结构落地：从 E-R 模型到可执行的 DDL

3.1 患者表和科室表：先搞定基础档案

患者表是门诊系统的数据根基，所有统计最终都要回归到这个主键上。设计时把患者基本信息和就诊信息拆开，患者表里只放姓名、性别、出生日期、证件号、手机号这类静态属性。证件号虽然是业务上的唯一标识，但存在少数患者证件缺失的情况，所以不要直接拿它当主键，用自增 ID 更稳妥，同时给证件号加唯一索引做防重。

科室表字段少，但要注意层级问题。如果门诊和急诊是平级科室，直接用一个 parent_id 自关联就够。课程设计里不要过度设计，做成两级足够。科室编码建议单独设计字段，用固定长度的字符串，方便做报表里按编码段筛选。

CREATE TABLE patient ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '患者主键', patient_no VARCHAR(32) NOT NULL COMMENT '患者编号', name VARCHAR(64) NOT NULL COMMENT '患者姓名', gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别 0未知 1男 2女', birth_date DATE DEFAULT NULL COMMENT '出生日期', id_card_no VARCHAR(18) DEFAULT NULL COMMENT '证件号码', phone VARCHAR(20) DEFAULT NULL COMMENT '手机号', reg_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建档时间', PRIMARY KEY (id), UNIQUE KEY uk_patient_no (patient_no), UNIQUE KEY uk_id_card (id_card_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='患者档案表';

patient_no 和自增主键同时存在，是因为患者编号是面向业务人员展示的，可能包含年份和流水号规则，而主键只服务于内部关联。id_card_no 允许为空，但空值情况下唯一索引不会冲突，这正好符合业务诉求。

3.2 挂号表：连接患者、医生和时间

挂号表是门诊系统里数据量增长最快的表，也是查询压力最集中的表。核心设计原则是：只存描述「一次就诊」的必要信息，不混入诊断和处方内容。一个挂号记录对应一个患者、一个医生、一个就诊时段，外加挂号费用和状态。

关键字段选择上，就诊日期用 DATE 类型，就诊时段用 VARCHAR 存上午或下午，这样统计日门诊量时直接按 visit_date 分组。挂号状态建议用 TINYINT，并放一个诊室字段。医生和科室同时出现在挂号表里不是冗余，因为科室可能调整归属，保留当时接诊科室的信息用于回溯。

CREATE TABLE registration ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '挂号主键', reg_no VARCHAR(32) NOT NULL COMMENT '挂号流水号', patient_id BIGINT UNSIGNED NOT NULL COMMENT '患者ID', doctor_id BIGINT UNSIGNED NOT NULL COMMENT '医生ID', dept_id BIGINT UNSIGNED NOT NULL COMMENT '科室ID', visit_date DATE NOT NULL COMMENT '就诊日期', time_slot TINYINT NOT NULL COMMENT '时段 1上午 2下午', reg_fee DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '挂号费', status TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0待接诊 1接诊中 2已完成 3已退号', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (id), UNIQUE KEY uk_reg_no (reg_no), KEY idx_patient_visit (patient_id, visit_date), KEY idx_doctor_date (doctor_id, visit_date), KEY idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='挂号记录表';

外键在课程设计中通常要求必建，但实际生产里门诊系统一般只用逻辑外键。原因是高并发写入场景下物理外键会拖慢插入性能。如果任课老师明确要求物理外键，可以在建表后单独添加 FOREIGN KEY，逻辑上不影响。

3.3 处方主表和明细表：一对多关系的标准解法

处方必须拆成主表和明细表。主表记录一张处方的整体信息：对应哪次挂号、哪个医生开的、开立时间、处方总金额、当前状态。明细表记录每一行药品：药品 ID、数量、单价、金额。拆开的直接收益是支持部分退费和按药品统计用量。

处方总金额这个字段值得讨论。它既可以从明细表聚合算出，也可以在明细写入时冗余存储。课程设计里建议两处都做，主表冗余金额字段能简化缴费查询，但要在业务逻辑层保证同步更新，不能让两边数据漂移。

CREATE TABLE prescription ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '处方主键', presc_no VARCHAR(32) NOT NULL COMMENT '处方编号', reg_id BIGINT UNSIGNED NOT NULL COMMENT '挂号ID', doctor_id BIGINT UNSIGNED NOT NULL COMMENT '开方医生ID', presc_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开立时间', total_amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '处方总金额', status TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未缴费 1已缴费 2已取药 3已退费', PRIMARY KEY (id), UNIQUE KEY uk_presc_no (presc_no), KEY idx_reg_id (reg_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='处方主表'; CREATE TABLE prescription_detail ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '明细主键', presc_id BIGINT UNSIGNED NOT NULL COMMENT '处方ID', drug_id BIGINT UNSIGNED NOT NULL COMMENT '药品ID', drug_name VARCHAR(128) NOT NULL COMMENT '药品名称冗余', quantity INT NOT NULL COMMENT '数量', unit_price DECIMAL(10,2) NOT NULL COMMENT '单价', amount DECIMAL(10,2) NOT NULL COMMENT '金额小计', PRIMARY KEY (id), KEY idx_presc (presc_id), KEY idx_drug (drug_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='处方明细表';

明细表里冗余 drug_name 是刻意的。药品名称可能会在药品基础表中调整，但处方明细属于历史数据，必须保留开单时的名称快照。这个设计思路在课程设计答辩时是加分点，说明你理解信息回溯的价值。

4. 索引和完整性约束，门诊高频操作的优化重点

4.1 外键策略：什么时候建物理外键

门诊管理系统的写入集中在挂号、处方、缴费三个节点，查询集中在统计报表和患者历史记录。物理外键在写入时会对父表加锁，影响并发能力，所以生产环境通常只建索引不建约束。但课程设计更看重规范性和完整性表达，建议在关系紧密的核心表上保留外键，比如 prescription_detail 的 presc_id 指向 prescription.id。

外键会带来一个直接问题：删除数据的顺序被锁定。比如要删除一个已归档的挂号记录，必须先删除它关联的处方明细和处方主表。实际操作中门诊数据几乎不做物理删除，都用状态位做软删除，所以外键的实际约束作用有限，更多是表达模型关系。

4.2 按查询模式设计组合索引

门急诊统计有一个高频查询：统计某科室某天各医生的接诊量。这个查询的条件是 dept_id 和 visit_date，分组是 doctor_id。单独的 dept_id 索引或 visit_date 索引都能走，但组合索引能避免回表。索引顺序按选择性从高到低排列，这里 dept_id 区分度高于 visit_date，所以放在前面。

-- 高频查询：科室日接诊统计 SELECT doctor_id, COUNT(*) AS patient_cnt FROM registration WHERE dept_id = 5 AND visit_date = '2025-03-10' GROUP BY doctor_id; -- 对应组合索引 ALTER TABLE registration ADD INDEX idx_dept_date (dept_id, visit_date);

组合索引建好后，单查 dept_id 也能走这个索引，相当于一个索引覆盖两个查询场景。反过来把 visit_date 放前面就做不到这一点，因为日期维度区分度低，查询优化器可能放弃索引。这个取舍是索引设计里最常见的考点。

4.3 数据一致性：用触发器还是用应用层控制

退号操作会连带影响挂号状态和处方状态。如果挂号已缴费但未取药，退号时必须同步把处方状态改为已退费，同时回补药品库存。这些操作在多张表之间保持一致，单靠应用代码容易漏掉某个分支。教学场景里可以用触发器兜底，生产环境则建议事务加应用层校验。

DELIMITER // CREATE TRIGGER trg_reg_cancel AFTER UPDATE ON registration FOR EACH ROW BEGIN IF NEW.status = 3 AND OLD.status <> 3 THEN UPDATE prescription SET status = 3 WHERE reg_id = NEW.id AND status = 1; END IF; END// DELIMITER ;

触发器的逻辑用一句话说明：当挂号的 status 从其他值更新为 3（已退号）时，把该挂号下所有已缴费的处方同步改为已退费。触发器的优点是逻辑跟随数据库，缺点是排查问题时不如应用层日志直观，而且在大事务里容易成为性能瓶颈。课程设计里两种方案选一个讲清楚即可。

5. 用 PowerDesigner 生成 E-R 图的落地技巧

5.1 从概念模型到物理模型的转换路径

PowerDesigner 设计数据库表 E-R 图时，最常见却不推荐的做法是直接在 Physical Data Model 里建表，因为这样跳过了概念层，画出来的图只是表结构面板，无法表达实体关系。正确的路径是先在 Conceptual Data Model 中定义实体和关系，再用 Transform 功能转换为 Physical Data Model。

概念模型中实体属性可以不指定数据类型，这是刻意为之，目的是专注在实体和基数分析上。转换时在 Tools 菜单选择 Generate Physical Data Model，再把逻辑类型映射成 MySQL 对应的物理类型。如果只是课程设计，不需要追求完全自动化，转换后手工修正字段类型反而更可控。

表与表之间的连线关系，PowerDesigner 默认用实线表示 identifying relationship，虚线表示非标识关系。门诊系统里挂号到处方是典型的非标识关系，因为处方的主键是独立的，不依赖挂号主键。在画图上把这两种连线区分清楚，评审老师一眼就能看出你理解了关系的语义差异。

5.2 检查模型的三个必要动作

PowerDesigner 画完图后，课程设计交付前需要检查三个点，也都是评审中最容易挑出的问题。

第一，每个实体是否都有主键标识。PowerDesigner 中用钥匙图标标注主键，没有主键的实体在图里会缺失主键标识，导出 DDL 时也会被跳过。第二，关系的基数方向是否和需求一致，在关系连线上双击可以查看 Cardinality 属性，确保一对一、一对多和实际业务对应。第三，字段注释是否完整，PowerDesigner 导出 SQL 时 Comment 会变成 MySQL 的 COMMENT 子句，没有注释的表结构在答辩现场很难讲清楚字段含义。

如果 DDL 脚本在 PowerDesigner 中生成的格式和 MySQL 不兼容，比如反引号缺失，可以在 Database Generation 界面勾选 Generate name in column 选项，并把 SQL 方言切到 MySQL 5.0 以上版本。生成脚本后不要直接运行，先检查自增列的 KEY 属性和字符串类型的长度是否合理。课程设计的评分通常在模型图、DDL 文档和答辩陈述三个维度展开，把 PowerDesigner 的模型图、关系基数说明和 MySQL 执行结果三者对应起来，这套设计就足够完整了。

本文还有配套的精品资源，点击获取