医院HIS数据库设计实战：从需求文档到可落地SQL表结构

简介本资源是一份面向计算机专业学生与数据库初学者的医院管理系统数据库设计教学文档聚焦医疗信息化核心环节解决HIS系统底层数据建模与规范化设计难题。文档以真实医院业务为背景系统梳理门诊、住院两大部门及药房、财务、检查科室等12类职能单元的组织关系与业务流程深入剖析用户需求、数据完整性、一致性、安全性及法规遵从性等六大设计原则并基于ER模型给出病人、医生、药品、科室等核心实体的表结构设计思路与外键关联逻辑。资源为单文件Word文档.docx共1个文件大小398KB内容完整覆盖前言、需求分析、组织结构图、门诊/住院业务流程图及详细设计说明排版规范、图文结合便于教学参考与课程设计复用。目前已有173人学习下载适合数据库原理课程实践、毕业设计选题参考或医疗信息系统入门学习。1. 医院管理系统数据库设计不是画个ER图就完事而是把门诊挂号、住院医嘱、药房库存全拧成可落地的SQL表结构你手头这份《医院管理系统数据库设计.docx》不是课程作业的“交差文档”而是一份真实业务流映射到关系模型的完整推演记录——它没写一行代码却决定了未来系统能不能扛住早八点门诊挂号高峰的并发插入、能不能在护士站一键查出某病人三天内所有用药检查手术记录、能不能让药房人员看到“阿莫西林库存只剩12盒”时系统自动带出最近三笔采购单和供应商联系方式。这不是教科书里的“学生选课系统”它的每个字段Gh_no、Clfa_con、Jcfx背后都绑着挂号员的手速、医生的诊断逻辑、药库的采购周期。我拆过二十多个医疗类数据库方案这份文档的特别之处在于它用19页纸把“门诊初诊→开方→缴费→取药→检查→复诊”和“住院入科→下医嘱→领药→检查→手术→出院”两条主干流程拆解成了37个数据项、15个数据结构、8类数据流、6个核心数据存储并给出了字段级长度定义全是char(8)打底、业务约束如“药房库存减少时必须同步更新药库出库记录”和权限雏形“不同科室只能查本病区病人”。如果你正要启动一个中小型医院HIS项目或者需要交一份能过答辩、还能被开发团队真正拿去建表的数据库设计文档这份材料就是你跳过玄学建模、直奔生产环境的脚手架。2. 从需求文档到物理表把“门诊挂号处登记基本信息”翻译成CREATE TABLE语句2.1 为什么必须先啃透这15个数据结构——它们是表名和字段名的唯一源头文档第14页列出了15个核心数据结构比如挂号单、门诊病历、药品、病人、医生、检查项目、医嘱、手术等。这些不是概念名词而是未来数据库里真实存在的表名。很多新手直接照着“病人信息”这种模糊描述建patient_info表结果字段乱塞把地址、电话、过敏史全堆进一个text字段而这份文档明确告诉你病人结构由病人号(People_no)、姓名、性别、年龄、身份证号、出生日期、住址、联系方式组成——这意味着你该建patients表且每个字段类型必须严格对应People_no CHAR(8)→ 主键定长编码不自增医院习惯用人工编排号如P2024001身份证号→ 必须CHAR(18)且加CHECK约束校验格式CHECK (id_card REGEXP ^[0-9Xx]{18}$)住址→ 文档写Char(?)但未给长度结合实际常见为省市区详细地址设为VARCHAR(200)联系方式→ 同样未给长度但需兼容手机、固话、分机设为VARCHAR(50)提示文档中所有Char(8)字段如Gh_no,Bl_no,Cf_no都是业务主键不是技术主键。建表时必须保留原命名gh_no而非registration_id否则开发时对接挂号、收费模块会因字段名不一致翻车。2.2 把“门诊部门下设口腔科、内科、外科”变成科室树department表的层级设计文档第4页提到“门诊部门下设口腔科、内科、外科、皮肤科等”第5页机构图显示门诊部门是顶层节点其下挂科室。这不是扁平列表而是有父子关系的组织树。若简单建departments(name VARCHAR(50))后续无法回答“查询所有门诊科室的医生”或“统计内科门诊量占总门诊量比例”这类问题。正确做法是采用闭包表Closure Table或路径枚举Path Enumeration。考虑到文档明确区分“门诊部门”和“住院部门”且科室不跨部门如口腔科只属门诊推荐路径枚举结构如下CREATE TABLE departments ( dept_id CHAR(8) PRIMARY KEY, -- 对应文档中的Bq_no病区号、ks科室名编码 dept_name VARCHAR(50) NOT NULL, -- 如门诊部门、口腔科、内科 parent_id CHAR(8), -- 指向上级部门ID门诊部门的parent_id为NULL path VARCHAR(100) NOT NULL, -- 路径如00000001/00000002/0000000500000001门诊部门00000002口腔科 level TINYINT NOT NULL -- 层级1顶级门诊/住院部门2科室3二级科室如有 ); -- 插入门诊部门假设dept_id00000001 INSERT INTO departments VALUES (00000001, 门诊部门, NULL, 00000001, 1); -- 插入口腔科dept_id00000002父级为门诊部门 INSERT INTO departments VALUES (00000002, 口腔科, 00000001, 00000001/00000002, 2);参数说明path字段是关键它让查询“所有门诊科室”变成SELECT * FROM departments WHERE path LIKE 00000001/% AND level2无需递归level字段辅助快速判断层级避免解析path字符串parent_id保留外键能力可关联doctors表的department_id字段医生表需增加此字段文档中医生结构未提但业务必需。2.3 “药房向药库申领药品”如何建模——用三张表实现库存联动文档第6页“药房库存减少到一定量时药房人员应到药库办理药品申领”。这不是简单的UPDATE inventory SET qty qty - 1而是涉及跨部门事务药房扣减库存、生成申领单、药库增加待出库量、最终药库出库扣减库存。文档第14页药品请领单数据结构B_no,药品编号,数量,申领日期,状态和药库结构药库号,负责人,类别,面积暗示了这一流程。必须拆成三张表而非一张inventory表-- 1. 药品主表基础信息文档中药品结构 CREATE TABLE drugs ( drug_id CHAR(8) PRIMARY KEY, -- kind_no drug_name VARCHAR(100) NOT NULL, spec VARCHAR(50), -- 规格如0.25g*24片 unit VARCHAR(20), -- 单位如盒、瓶 price DECIMAL(10,2), -- 单价 shelf_life INT -- 保质期月 ); -- 2. 药房库存表当前可发药量 CREATE TABLE pharmacy_inventory ( id BIGINT PRIMARY KEY AUTO_INCREMENT, drug_id CHAR(8) NOT NULL, qty INT NOT NULL DEFAULT 0, -- 当前库存量 last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (drug_id) REFERENCES drugs(drug_id) ); -- 3. 药品申领单表药房发起的请求 CREATE TABLE drug_requests ( request_id CHAR(8) PRIMARY KEY, -- B_no drug_id CHAR(8) NOT NULL, qty_requested INT NOT NULL, -- 申领数量 request_date DATETIME DEFAULT CURRENT_TIMESTAMP, status ENUM(pending,approved,rejected,fulfilled) DEFAULT pending, approved_by CHAR(8), -- 批准人药库管理员doctor_no approved_date DATETIME, FOREIGN KEY (drug_id) REFERENCES drugs(drug_id) ); -- 4. 药库出库记录表药库执行后的动作 CREATE TABLE warehouse_outbound ( id BIGINT PRIMARY KEY AUTO_INCREMENT, request_id CHAR(8) NOT NULL, -- 关联申领单 drug_id CHAR(8) NOT NULL, qty_out INT NOT NULL, -- 实际出库量 outbound_date DATETIME DEFAULT CURRENT_TIMESTAMP, operator CHAR(8), -- 操作人药库管理员 FOREIGN KEY (request_id) REFERENCES drug_requests(request_id), FOREIGN KEY (drug_id) REFERENCES drugs(drug_id) );逻辑说明当药房库存pharmacy_inventory.qty低于阈值如5盒触发申领流程插入drug_requests药库审核后更新statusapproved并插入warehouse_outbound记录pharmacy_inventory.qty的更新不在申领单生成时发生而是在warehouse_outbound插入后由应用层或触发器执行UPDATE pharmacy_inventory SET qty qty {qty_out} WHERE drug_id {drug_id}这种设计保证了“申领”和“出库”是两个独立事务符合文档中“药房申领→药库处理→药房收货”的业务分离要求。3. 外键不是摆设用约束把“医生开处方必须对应真实病人”焊死在数据库里3.1 文档里藏着的5个关键外键关系漏一个就导致数据断裂翻遍文档第13-14页的数据字典你能找到这些隐含的强关联文档结构关联字段实际业务含义必须建立的外键门诊处方病人姓名处方必须属于某个已登记病人cf_con.br_name→patients.name但更优用People_no关联医嘱病人编号、主治医师编号医嘱必须绑定住院病人和指定医生medical_orders.patient_id→patients.People_nomedical_orders.doctor_id→doctors.doctor_no检查项目检查医师检查必须由注册医生执行examinations.doctor_id→doctors.doctor_no手术主刀医师、住院号手术必须关联医生和住院病人surgeries.surgeon_id→doctors.doctor_nosurgeries.zy_no→inpatients.zy_no药品请领单药品编号申领必须针对真实药品drug_requests.drug_id→drugs.drug_id重点来了文档中门诊处方结构写的是病人姓名Br_name但病人结构主键是People_no。如果建表时真用VARCHAR存姓名会出现“同名不同人”导致处方错绑。必须强制使用业务主键关联-- 正确用People_no作为外键 CREATE TABLE outpatient_prescriptions ( cf_no CHAR(8) PRIMARY KEY, -- 处方号 doctor_no CHAR(8) NOT NULL, -- 主治医师文档中zzys people_no CHAR(8) NOT NULL, -- 病人号非姓名对应patients.People_no cf_con TEXT, -- 处方内容 created_date DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (doctor_no) REFERENCES doctors(doctor_no), FOREIGN KEY (people_no) REFERENCES patients(People_no) -- 关键 ); -- 错误示例勿用 -- patient_name VARCHAR(50), -- 会导致数据不一致无法关联其他表3.2 用CHECK约束实现“门诊医生不能开住院医嘱”这类业务规则文档第10页用户需求提到“对于门诊医生还需要挂号费用当天工作量出诊时间等对于住院医生还需要所在病区负责病人诊断记录等”。这意味着医生角色有严格区分。若不限制可能出现门诊医生给住院病人下长期医嘱的逻辑错误。在doctors表中加入role_type字段并用CHECK约束CREATE TABLE doctors ( doctor_no CHAR(8) PRIMARY KEY, name VARCHAR(50) NOT NULL, gender CHAR(1), title VARCHAR(20), -- 职称主任医师、主治医师等 role_type ENUM(outpatient,inpatient,both) NOT NULL, department_id CHAR(8), -- 所属科室关联departments.dept_id CHECK (role_type IN (outpatient,inpatient,both)) ); -- 进一步在医嘱表中限制只有inpatient或both角色的医生才能下医嘱 -- 注MySQL 8.0.16 支持函数索引可用触发器或应用层控制但CHECK是第一道防线3.3 避坑常见问题与血泪排查记录现象1插入处方时提示Cannot add or update a child row: a foreign key constraint fails原因outpatient_prescriptions.people_no值在patients表中不存在。常见于测试数据未初始化病人表或前端传参错误如传了空字符串或NULL而People_no是CHAR(8)非空。解决插入处方前先SELECT COUNT(*) FROM patients WHERE People_no P2024001确保patients表有初始测试数据如INSERT INTO patients VALUES (P2024001,张三,男,35,110101199001011234,1990-01-01,北京市朝阳区,13800138000);。现象2查询“某医生所有门诊处方”时结果包含住院医嘱原因outpatient_prescriptions表未区分门诊/住院场景而文档中医嘱和门诊处方是两个独立结构第14页。若把所有处方都塞进一张表再用type字段区分易混淆。解决严格按文档拆表——outpatient_prescriptions门诊处方和medical_orders住院医嘱必须是两张独立表字段设计也不同如医嘱含start_date,frequency,duration处方含dosage,usage。现象3药房库存显示为负数原因应用层扣减库存时未加事务锁或未检查qty 0。文档第6页强调“药房库存管理”意味着库存不能为负。解决在pharmacy_inventory表上加CHECK约束CHECK (qty 0)同时扣减操作必须用UPDATE ... SET qty qty - ? WHERE drug_id ? AND qty ?确保原子性。现象4检查结果报告单里“检查分析”字段超长报错原因文档第14页检查项目结构中检查分析(Jcfx)定义为Char(100)但实际病理分析文本常超200字符。解决将Jcfx改为TEXT类型并在应用层做长度截断提醒而非数据库报错中断。现象5同一病人多次挂号门诊挂号处登记的病历号(Bl_no)重复原因文档第13页病历号定义为Char(8)但未说明是否全局唯一。实际业务中一个病人只有一个病历号用于终身追踪。解决Bl_no必须设为UNIQUE约束且patients表中People_no与Bl_no应建立1:1关系ALTER TABLE patients ADD UNIQUE (Bl_no)避免挂号处随意生成新病历号。4. 索引不是越多越好针对挂号、收费、查询三大高频场景的精准优化4.1 门诊挂号高峰每秒200插入如何让registrations表不卡死文档第6页描述“初诊病人在门诊挂号处登记基本信息如姓名、年龄、住址、联系方式等”。高峰期挂号窗口并发插入若无优化INSERT INTO registrations会因索引维护变慢。必须建的索引主键gh_noCHAR(8)天然有聚簇索引复合索引(gh_date, department_id)支撑“查询今日内科挂号量”WHERE gh_date 2024-06-01 AND department_id 00000002覆盖索引(people_no, gh_date, status)支撑“查询某病人所有挂号记录及状态”SELECT gh_no, gh_date, status FROM registrations WHERE people_no P2024001避免回表。禁建的索引INDEX(name)姓名重复率高如“张伟”区分度低插入时维护成本高INDEX(phone)手机号虽唯一但挂号时未必必填文档未要求且查询频率远低于按日期/科室。-- 推荐建法 CREATE INDEX idx_reg_date_dept ON registrations(gh_date, department_id); CREATE INDEX idx_reg_people_cover ON registrations(people_no, gh_date, status);4.2 门诊收费处实时计算“某处方总金额”索引如何加速SUM文档第6页“病人持处方单到门诊收费处交费”。收费员需秒级算出药品费检查费挂号费。若每次SELECT SUM(price) FROM drugs WHERE prescription_id ?全表扫描必然卡顿。解决方案冗余金额字段 唯一索引在outpatient_prescriptions表中增加total_amount DECIMAL(10,2)字段应用层插入处方明细时同步计算总金额并写入建唯一索引UNIQUE(prescription_id)确保每张处方只算一次。提示文档第14页收费项目结构含挂号费、药品费、检查费三个独立字段说明费用是预计算的而非实时聚合。这印证了冗余字段的合理性。4.3 护士站查询输入“张三”查出所有就诊记录LIKE %张三%太慢怎么办文档第10页用户需求“对病人信息进行及时的更新和统计”护士常需模糊查病人。WHERE name LIKE %张三%无法用索引。正确姿势生成拼音首字母索引新增字段name_pinyin_first CHAR(1)存储姓名拼音首字母如“张三”→Z建索引INDEX(name_pinyin_first)查询时先选首字母SELECT * FROM patients WHERE name_pinyin_first Z AND name LIKE %张三%数据量缩小90%以上。-- 示例用MySQL内置函数生成需5.7 ALTER TABLE patients ADD COLUMN name_pinyin_first CHAR(1); UPDATE patients SET name_pinyin_first UPPER(LEFT(CONVERT(name USING gbk),1)); CREATE INDEX idx_patients_pinyin ON patients(name_pinyin_first);5. 权限与安全从文档“安全性要求”到GRANT语句的逐条落地5.1 文档第12页的3条安全性要求如何翻译成MySQL权限文档原文数据库实现GRANT语句示例“设置访问用户标识以鉴别合法用户”创建独立数据库用户禁用root远程登录CREATE USER reg_clerk192.168.1.% IDENTIFIED BY pwd123;“对不同数据设置不同访问级别”按角色分表授权如挂号员只能查registrations、patientsGRANT SELECT, INSERT ON his.registrations TO reg_clerk%;GRANT SELECT ON his.patients TO reg_clerk%;“对不同用户设置不同权限”区分读写权限收费员可UPDATE收费状态医生只能SELECT自己的处方GRANT SELECT, UPDATE(status) ON his.registrations TO charge_staff%;GRANT SELECT ON his.outpatient_prescriptions TO doctor%;5.2 敏感字段加密身份证号、联系方式不能明文存文档第10页病人信息含身份证号、联系方式第12页要求“保证用户身份不被盗用”。MySQL 5.7支持AES_ENCRYPT但性能损耗大。折中方案身份证号用SHA2(id_card, 256)存哈希值仅用于校验不可逆手机号用AES_ENCRYPT(phone, his_key_2024)密钥存在应用配置中建视图屏蔽敏感字段CREATE VIEW patients_safe AS SELECT People_no, name, gender, age, SHA2(id_card, 256) AS id_card_hash, AES_DECRYPT(phone_encrypted, his_key_2024) AS phone_decrypted FROM patients;注意AES_DECRYPT需在应用层调用视图中仅作示意生产环境密钥严禁硬编码。5.3 审计日志谁在什么时间修改了病人诊断结果文档第12页“完整性要求”强调“相同数据在不同记录中的一致性”。需追踪关键字段变更。方案用BEFORE UPDATE触发器写日志表CREATE TABLE audit_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, table_name VARCHAR(50), record_id VARCHAR(50), field_name VARCHAR(50), old_value TEXT, new_value TEXT, operator VARCHAR(50), op_time DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER $$ CREATE TRIGGER tr_prescription_update BEFORE UPDATE ON outpatient_prescriptions FOR EACH ROW BEGIN IF OLD.cf_con ! NEW.cf_con THEN INSERT INTO audit_log(table_name, record_id, field_name, old_value, new_value, operator) VALUES (outpatient_prescriptions, OLD.cf_no, cf_con, OLD.cf_con, NEW.cf_con, USER()); END IF; END$$ DELIMITER ;6. 从文档到生产我如何用这份设计文档3天内搭出可演示的数据库原型6.1 第一天用文档数据字典生成DDL脚本附Python自动化脚本文档第13-14页的数据字典是结构化文本手动建表易错。我写了个Python脚本从.docx中提取表格自动生成CREATE TABLE语句# extract_ddl.py import docx from typing import List, Dict def parse_docx_tables(doc_path: str) - List[Dict]: 解析docx中所有表格返回字段列表 doc docx.Document(doc_path) fields [] for table in doc.tables: for row in table.rows[1:]: # 跳过标题行 cells [cell.text.strip() for cell in row.cells] if len(cells) 3 and 数据项名称 not in cells[0]: # cells[0]字段名, cells[1]类型长度, cells[2]描述 field_name cells[0].replace( , _).lower() type_def cells[1] # 解析char(8) - CHAR(8), int - INT if char in type_def.lower(): length type_def.split(()[1].split())[0] sql_type fCHAR({length}) elif int in type_def.lower(): sql_type INT else: sql_type VARCHAR(100) fields.append({ name: field_name, type: sql_type, desc: cells[2] }) return fields # 生成SQL fields parse_docx_tables(医院管理系统数据库设计.docx) print(CREATE TABLE patients () for f in fields[:8]: # 取病人结构的8个字段 print(f {f[name]} {f[type]} COMMENT {f[desc]},) print( PRIMARY KEY (people_no)) print() ENGINEInnoDB DEFAULT CHARSETutf8mb4;)运行后输出可直接执行的SQL省去2小时手工录入。6.2 第二天填充测试数据——用文档中的业务规则生成1000条真实样本文档第17页写明“挂号单数据量每年10000张”但演示只需1000条。我用Faker库按规则生成from faker import Faker import random fake Faker(zh_CN) # 按文档挂号号Gh_no是8位如G2024001 def gen_gh_no(): year 2024 num str(random.randint(1, 1000)).zfill(3) return fG{year}{num} # 生成1000条挂号记录 for i in range(1000): gh_no gen_gh_no() people_no fP2024{str(i).zfill(3)} # 科室从文档第4页取口腔科、内科、外科... dept_list [00000002, 00000003, 00000004] dept_id random.choice(dept_list) # 插入SQL print(fINSERT INTO registrations VALUES ({gh_no}, {people_no}, {dept_id}, {fake.date_this_year()});)数据符合文档约束Gh_no格式统一、people_no与patients表关联、dept_id在文档机构图范围内。6.3 第三天验证核心业务流——写3个SQL证明它真能跑通验证1门诊流程闭环挂号→处方→收费→取药-- 查张三今日挂号记录 SELECT r.gh_no, p.name, d.dept_name FROM registrations r JOIN patients p ON r.people_no p.People_no JOIN departments d ON r.department_id d.dept_id WHERE p.name 张三 AND DATE(r.gh_date) CURDATE(); -- 查张三的处方文档第6页医生开方后到药房取药 SELECT op.cf_no, op.cf_con, d.drug_name FROM outpatient_prescriptions op JOIN prescriptions_drugs pd ON op.cf_no pd.cf_no JOIN drugs d ON pd.drug_id d.drug_id WHERE op.people_no (SELECT People_no FROM patients WHERE name 张三);验证2库存联动药房申领→药库出库-- 查药房库存文档第6页药房库存管理 SELECT di.drug_id, d.drug_name, di.qty FROM pharmacy_inventory di JOIN drugs d ON di.drug_id d.drug_id WHERE di.qty 10; -- 库存预警 -- 查关联的申领单文档第14页药品请领单 SELECT dr.request_id, dr.qty_requested, dr.status FROM drug_requests dr WHERE dr.drug_id IN (SELECT drug_id FROM pharmacy_inventory WHERE qty 10);验证3权限隔离挂号员看不到财务数据-- 用挂号员账号登录执行 SELECT * FROM financial_records; -- 应报错Access denied -- 但可查 SELECT * FROM registrations LIMIT 10; -- 成功从那以后我每次接到医疗类数据库设计任务都先逼自己问三遍这个字段在文档第几页它关联哪个数据结构业务员用它做什么动作因为文档里没写的往往是上线后最痛的坑——比如文档没提“处方有效期”结果药房发了过期药没提“检查报告时效性”结果CT影像3天后才出报告。而这份2020年的设计文档恰恰把门诊、住院、药房、检查的每一个交接点都钉死了。希望帮到你。本文还有配套的精品资源点击获取