人事管理系统数据库设计：六表建模与状态/历史分离避坑

简介这份文档面向高校计算机与信息管理专业学生以人事管理系统后台数据库为设计题目覆盖从需求分析到数据库实施、再到报告撰写的完整流程适合正在完成数据库课程设计或准备相关实验的人对照参考。压缩包内只有1个doc文件约1.23MB即课程设计报告正文。内容按任务书、需求分析、概念设计、逻辑设计、物理设计、数据库实施、功能实现与总结的顺序展开包含多部门与员工、考勤等业务的功能要求数据字典与数据项定义实体关系与表结构、主键外键及完整性约束的说明以及在SQL Server环境下建表、索引与存储过程的实现思路。报告还给出按年月统计职工出勤、按日期查询部门迟到早退人数、按年统计部门调入调出等统计功能的实现方案并列出参考书目。已有1110人学习。1. 人事管理系统里最贵的不是存储是时间一个几百人规模的公司六张表跑十年也未必撑满一个 GB所以「数据库 课程设计」里做人事管理难点从来不在存不下。真正难的是时间某员工三月在技术部、五月调去财务部、七月离职那么八月的考勤该不该给他记、工资表里那一行到底归哪个部门的成本中心——这些答案没法在同一条记录里同时成立。这份人事管理系统设计做的事情本质是把「现在是什么」和「曾经是什么」拆成两套结构基本信息、工作信息、部门、工资、考勤各管一摊而调动的历史单独落一张表。搞懂这个拆分的动机比背熟建表语法有用得多因为现实里翻车的设计多半都是把状态和轨迹塞进了同一行。2. 需求分析到数据字典字段的取值域为什么在建表之前就决定了需求分析阶段最容易走过场列一堆功能点画一张流程图然后直奔建表。但这份设计里真正有信息量的是那张看起来最枯燥的数据项表——每一个字段的类型、长度、取值范围其实是在替后面所有的业务规则做担保。2.1 数据项的取值域就是业务语义的合同数据字典通常拆成五块数据项、数据结构、数据流、数据存储、处理过程。前两块决定表结构后三块决定程序怎么调它。把这个框架套到考勤上就很有意思缺勤、迟到、早退三个字段都定义为int(1)取值 0 或 1。这个取值域本身就说明了语义——系统只关心「这天有没有迟到」不关心「迟到了几分钟」。一旦你想加「迟到扣多少钱按分钟算」这个取值域就立刻不够用了。常见的做法是把字段从 0/1 改成分钟数或者干脆新建一张打卡明细表存每次打卡时间由程序去算迟到。前者改动最小但语义变了字段名得跟着改否则半年后没人知道late 1到底是「迟到一次」还是「迟到一分钟」。同一张表里还有两处更隐蔽的取值域问题年龄和工龄都被定义成定长整数。年龄int(3)、工龄int(2)看着合理但这两个值每年都在变。今天写进去 28 岁明年这条记录就是错的除非有人每年跑一次批量刷新。这是典型的「会腐烂的字段」——它本来可以由入职时间和出生日期算出来属于派生数据。提示凡是能由其它字段算出来的值落表前先问一句「它多久会过期」。会过期的字段要么改成存原始日期要么在应用层算不要两边都存。对照一下这份设计里的处理方式就很清楚入职时间是date存的是原始事实永远不过期工龄是int(2)存的是计算结果一年就烂一次。两相对照哪种更稳当一目了然。2.2 七个功能模块如何收敛成六张表需求里列了七个模块基本信息、工作信息、部门信息、考勤、工资、调动、查询统计。前六个模块一一对应六张表第七个模块不建表——它是对前六张表的聚合视图。为什么不给「查询统计」单独建汇总表因为汇总表是派生数据一旦源表改了而汇总表没跟着改两份数据就打架排查起来极其痛苦。汇总表不是不能建而是要有明确的刷新机制和允许延迟的预期那属于数据仓库的活儿不在课程设计的射程里。更值得琢磨的是工资为什么必须独立成表而不是塞进员工表。原因有两条第一工资是按月产生的一个员工一年十二条塞进员工表就变成了多值字段直接违反第一范式第二工资的可见性和基本信息完全不同员工能看自己的姓名电话但不一定能看全公司的薪酬。把权限边界不同的数据放在同一张表里后面只能靠视图去切反而更乱。真正卡住施工顺序的是部门表和一个隐含关系部门表里有部门经理而经理本身是员工。于是部门表要引用员工表员工表又要引用部门表两个CREATE TABLE互相等待。血泪经验就是——不要试图在一条语句里解决它。-- 第一步先建部门表经理字段暂时不挂外键 CREATE TABLE department_info ( department_id INT NOT NULL PRIMARY KEY, department_name NVARCHAR(20) NOT NULL, department_phone VARCHAR(11) NULL, manager_id INT NULL -- 先留空等员工表就位再补约束 ); -- 第二步建员工表此时部门表已存在外键可以直接写 CREATE TABLE staff_info ( staff_id INT NOT NULL PRIMARY KEY, name NVARCHAR(10) NOT NULL, gender NVARCHAR(4) NOT NULL, id_card CHAR(18) NOT NULL, hire_date DATE NOT NULL, department_id INT NOT NULL, phone VARCHAR(11) NOT NULL, base_salary DECIMAL(10,2) NOT NULL, CONSTRAINT fk_staff_dept FOREIGN KEY (department_id) REFERENCES department_info(department_id) ); -- 第三步回头补上部门经理的外键打破循环引用 ALTER TABLE department_info ADD CONSTRAINT fk_dept_manager FOREIGN KEY (manager_id) REFERENCES staff_info(staff_id);这三步的顺序不能反fk_staff_dept要求部门表先存在fk_dept_manager要求员工表先存在而员工表又依赖部门表唯一的解法是把其中一个外键推迟到建表之后再ALTER。参数上要注意manager_id允许为空——新成立的部门可能还没来得及任命经理如果设成NOT NULL这条插入直接被拒业务上会很难受。顺带说一个类型选择姓名字段用NVARCHAR(10)而不是VARCHAR(10)。在中文排序规则下VARCHAR的长度按字节算一个汉字占两字节VARCHAR(10)只能放下五个汉字NVARCHAR(10)按字符算十个汉字稳稳当当。数据字典里写「四个汉字以内」是保守估计但如果哪天来了个复姓加少数民族名字的员工五个字的VARCHAR就会截断。3. E-R 图转关系模式1:n 的外键为什么必须落在 n 端概念设计画完一堆局部 E-R 图到逻辑设计要转成关系模式这一步的规则书上都有但「为什么这么转」很少有人讲透。规则记不住的时候回到范式去想答案基本都能推出来。3.1 局部 E-R 图合并时要处理的三类冲突六张局部 E-R 图分别描述员工、工作、部门、工资、考勤、调动合并成全局图时会撞上三类冲突。命名冲突最常见员工编号在员工表里叫staff_id在调动表里也可能叫staff_id但部门编号在一处叫department_id、在另一处叫dep_id同义不同名合图时如果没统一后面写 JOIN 会出现「两个字段明明是一个东西却对不上」的尴尬。命名冲突还有反过来的——同名异义比如两张图里都有time字段一个指考勤日期一个指调动时间。结构冲突更隐蔽员工在部门 E-R 图里是实体在工资 E-R 图里可能被处理成「工资」实体的一个属性组。合并时必须统一成实体否则工资表里会莫名其妙多出一堆人员信息字段。属性冲突则是类型和单位上的分歧比如员工编号在一张图里是int在另一张图里是varchar——因为有人想保留前导零。这类冲突必须在逻辑设计阶段一次性拍板不能等到物理设计再改那时候表都建好了改类型要迁数据。3.2 把 1:n 的外键放错端会直接违反第一范式E-R 转关系模式的核心规则只有三条一个实体转一个关系模式实体的属性变字段实体的码变主键一个联系也转关系模式具有相同码的模式可以合并。真正需要动脑的是联系的码怎么定。1:1 联系两边实体的码都是候选码随便选一个当主键另一个做唯一约束。1:n 联系关系的码取 n 端实体的码。m:n 联系关系的码是两端实体码的组合。为什么 1:n 一定要落在 n 端假设部门与员工是 1:n如果把部门编号放进员工表一个员工一行一个部门编号干净利落。反过来把员工编号放进部门表一个部门多个员工这一格就得塞进一组编号。关系模型不允许字段里装集合这就是第一范式的硬约束。所以「一个部门有多个员工」这个事实只能用「多个员工行各自指向同一个部门」来表达方向是反的。按这个规则员工表里应该存的是department_id而不是部门名称字符串。但这份设计里员工信息表存的是varchar(20)的「所属部门」而员工工作表存的却是department_id。同一个事实存在两个地方还用了两种表示——一份是名字一份是编号。部门改名那天两份数据就开始互相打脸。这是逻辑设计阶段最该被揪出来的问题。3.3 冗余字段与函数依赖调动表里的姓名该不该留员工调动表里存了name字段这在规范化视角下是明显的冗余姓名已经在员工表里了靠staff_id就能关联出来。但真实系统里这种冗余非常普遍理由是查询方便调阅历史时不用再 JOIN 一次。判断能不能接受看两点。第一这个字段会不会变。姓名会变——员工改过名字、录入错误更正——一旦变了历史调动记录里的旧名字要不要跟着改如果业务要求「历史记录保留当时的名字」那这个冗余不是冗余是快照应当保留如果要求全系统一致那就该去掉靠 JOIN 取。第二冗余带来的不一致风险有没有兜底。常见的兜底方式是触发器或者在应用层统一写入口禁止绕过入口直接 UPDATE。同理工资表里存实发工资real_salary而它其实等于底薪加补贴加奖金加加班费减扣款。这是典型的派生字段。留在表里的好处是查询快、报表不用每次算坏处是任何一个组成项改了而实发工资没跟着改账就错了。业界的做法通常是把计算封在存储过程或应用层的一个方法里让「改组成项」和「改实发」永远成对出现而不是靠人自觉。3.4 主键选业务键还是代理键员工编号用int自增还是用身份证号这份设计选了前者。身份证号看着唯一、天然但它是 18 位字符末位可能是校验位字母不能存数值类型更关键的是它属于敏感信息一旦作为主键它会顺着外键渗透到考勤、工资、调动每一张表里权限控制会变得极其麻烦。代理键自增整数的好处是短、稳定、不含业务含义改身份证号不影响任何关联。代价是多一次唯一性校验——身份证号上加UNIQUE约束插入时要多查一次。对人事这种写入频率极低的场景这点代价完全可以接受。4. 物理设计到 SQL 实施文件分组、类型长度与视图封装的取舍逻辑设计确定「存什么」物理设计确定「怎么存」。这一步的每个选择都会在半年后以性能问题或者数据错误的形式回来找你。4.1 数据文件、次要数据文件、事务日志各管什么课程设计里提到数据库由主数据文件、次要数据文件和事务日志文件组成很多人背下来了但没想过为什么这么分。主数据文件记录数据库的元信息并存放数据页次要数据文件纯粹用来摊开数据把不同的表或索引放到不同物理磁盘上减少同一块盘的 I/O 争抢事务日志文件记录每一次修改的前后值是崩溃恢复和事务回滚的依据。日志必须和数据分开的原因在于写入模式完全不同数据页是随机读写日志是严格的顺序追加。混在一块盘上顺序写会被随机读打断日志写入延迟上来整个事务的提交都会变慢。这也是为什么日志文件不建议做频繁的收缩——收缩会把日志切成碎片下一次写入又得重新找连续空间。在 SQL Server 里这套东西的落地形式是文件组-- 主文件组放系统表和维度表次要文件组放大表和历史表 ALTER DATABASE hr_demo ADD FILEGROUP fg_history; ALTER DATABASE hr_demo ADD FILE (NAME Nhr_history, FILENAME ND:\data\hr_history.ndf, SIZE 64MB, FILEGROWTH 64MB) TO FILEGROUP fg_history;FILEGROWTH设成固定值而不是百分比是为了避免库大了以后每次自动增长都申请一大块、造成长时间的阻塞。SIZE给个起始值能减少前期频繁增长的次数。课程设计层面用不到这么细但知道这层存在以后看到生产库的ndf文件就不会一脸茫然。4.2int(6)、varchar(10)、float(6)到底约束了什么物理设计表里那几列类型和长度是本篇最值得较真的地方因为写错了不会立刻报错而是等到数据长到边界才炸。写法实际含义风险点int(6)SQL Server 不接受给int指定长度MySQL 里括号只是显示宽度不改变 4 字节存储和取值范围以为限定了 6 位实际能存到二十一亿varchar(10)按字节计长中文排序规则下 1 个汉字占 2 字节最多 5 个汉字姓名超长被截断或直接报错float(6)尾数位数float(1~24)等价于 4 字节的real只有 7 位有效数字金额累加出现小数误差date只存年月日3 字节和datetime混用做比较时边界那天的记录容易漏金额字段这条最要命。工资表里底薪、补贴、奖金、扣款、实发全用float单条看没问题一旦做部门汇总SUM(real_salary)尾数就可能出现12345.679999999这种结果。财务口径上这是不能接受的。正确做法是DECIMAL(10,2)定点存储两位小数累加不会漂。-- 金额统一改成定点数避免汇总时出现浮点尾差 ALTER TABLE staff_salary ALTER COLUMN basic_salary DECIMAL(10,2) NOT NULL; ALTER TABLE staff_salary ALTER COLUMN real_salary DECIMAL(10,2) NOT NULL;改类型前要先确认现有数据能无损转换float里的值如果已经带了七八位小数转成两位会四舍五入得先跑一遍差异对比别直接在生产上动手。身份证号同理。18 位里最后一位可能是X存int或bigint都存不下必须用CHAR(18)。用定长而不是变长是因为长度恒定为 18定长在比较时更快也省掉了长度前缀。4.3 完整性约束外键、CHECK 与「不要物理删除」参照完整性靠外键兜底这一点没有争议。真正需要决策的是删除行为。人事系统里删除一个员工意味着什么辞职、辞退、退休、转出本质上都不是「这条记录不存在了」而是「这个人的状态变了」。如果配ON DELETE CASCADE删掉员工会把他的考勤、工资、调动记录一并抹掉历史统计直接失真如果配NO ACTION删除会被外键拦下来只能先把子表清空操作繁琐但安全。常见的做法是软删除员工表加一个status字段和leave_date删除动作变成状态变更所有查询默认过滤status 在职。这样历史数据完整保留按年统计调入调出才有数据可用。代价是每个查询都要带条件忘了带就会把离职人员算进在职人数——这是另一个高频坑。-- 用状态位代替物理删除历史记录得以保留 ALTER TABLE staff_info ADD status VARCHAR(10) NOT NULL DEFAULT 在职, leave_date DATE NULL; ALTER TABLE staff_info ADD CONSTRAINT ck_staff_status CHECK (status IN (在职,离职,退休,借调));CHECK约束在这里的价值是防止应用层写入脏状态比如拼错的「在職」。数据库层拦一道比事后写脚本清洗便宜得多。4.4 视图封装 JOIN省事的同时也埋了雷功能实现部分建了五个视图把多表 JOIN 固化下来这个思路是对的——报表和查询不用每次重写连接条件字段别名也统一了。但这份设计里的视图有一个容易被忽略的陷阱。以「员工个人信息图」为例它的连接路径是部门表 → 调动表 → 员工表 → 工作表。问题在于调动表只有发生过调动的员工才有记录用INNER JOIN串起来结果就是——从来没调动过的员工在视图里查不到。一个刚入职、一直在同一个部门的人打开个人信息页会是空白。第二个问题更隐蔽一个员工如果调动过三次调动表里就有三行JOIN 之后这个人在视图里会出现三次。做COUNT(*)统计人数时数字直接翻倍。第三个问题是别名字段取错了来源。「原部门名称」和「新部门名称」都取自同一个department_info.department_name等于把新部门的名称同时当成了原部门的名称。合理的写法是让「当前隶属」以工作表为准调动表只负责历史轨迹-- 当前隶属关系从工作表取调动表不参与避免漏人和行膨胀 CREATE VIEW v_staff_current AS SELECT s.staff_id AS 员工编号, s.name AS 姓名, d.department_id AS 部门编号, d.department_name AS 部门名称, w.position AS 职称, w.work_age AS 工龄 FROM staff_info s JOIN staff_work w ON w.staff_id s.staff_id JOIN department_info d ON d.department_id w.department_id;三个JOIN从四张表降到三张语义反而更准工作表存的就是「这个人现在在哪个部门」视图直接读它不需要绕道调动记录去推断。调动视图则单独建专供历史追溯两个视图各司其职。有了这个基础统计类的查询才好写。按年月统计某职工的出勤关键是把「年月」翻译成日期区间而不是对字段做函数运算-- 用半开区间过滤日期能走索引对 sign_date 套 YEAR()/MONTH() 则必然全表扫描 SELECT s.staff_id, s.name, SUM(a.absent) AS 缺勤天数, SUM(a.late) AS 迟到次数, SUM(a.leave_early) AS 早退次数 FROM staff_signin a JOIN staff_info s ON s.staff_id a.staff_id WHERE a.staff_id 100001 AND a.sign_date 2019-03-01 AND a.sign_date 2019-04-01 GROUP BY s.staff_id, s.name; 月初 AND 下月初这个半开区间写法是踩过坑之后形成的肌肉记忆。写成BETWEEN 2019-03-01 AND 2019-03-31如果字段是DATETIME且存了时分秒3-31 14:00那条记录就漏掉了而写成WHERE YEAR(sign_date) 2019 AND MONTH(sign_date) 3索引直接失效数据量一大就慢得没法看。按部门统计某天的迟到早退人数麻烦点在于「某天这个员工属于哪个部门」可能和现在不一样。严格做法是查调动表里当天生效的那条记录-- 判断某员工在指定日期归属的部门取生效时间不晚于该日期的最后一条调动记录 SELECT t.new_dep_id FROM staff_transfer t WHERE t.staff_id 100001 AND t.new_time 2019-03-15 ORDER BY t.new_time DESC LIMIT 1;按年统计各部门调入调出用UNION ALL把两个方向拼起来再汇总比写两个相关子查询清楚得多SELECT 年份, 部门编号, SUM(调入) AS 调入人数, SUM(调出) AS 调出人数 FROM ( SELECT YEAR(new_time) AS 年份, new_dep_id AS 部门编号, 1 AS 调入, 0 AS 调出 FROM staff_transfer WHERE new_time IS NOT NULL UNION ALL SELECT YEAR(old_time), old_dep_id, 0, 1 FROM staff_transfer WHERE old_time IS NOT NULL ) x GROUP BY 年份, 部门编号;UNION ALL而不是UNION是因为这里本来就可能存在同一年同一部门既有人调入又有人调出的两行去重会把它们合成一行数字就少了。这类细节不明显但错一次就要重新对账。5. 避坑与排查五个最容易翻车的地方下面这几条都是这类系统在验收或者上线初期最常撞上的按「现象 → 原因 → 解决」记录方便对照排查。5.1 五条踩坑记录现象一建表脚本执行到一半报「无法添加外键约束」。原因是部门表和员工表互相引用脚本按字母序执行先建的那张表引用了一张还不存在的表。解决办法是把其中一个外键改成建表后的ALTER TABLE追加或者干脆先建两张不带外键的表最后统一补约束。更省事的做法是把整个脚本包在一个事务里报错就整体回滚不留半成品。现象二录入一个六字姓名的员工插入报「字符串或二进制数据将被截断」。原因是字段定义成了VARCHAR(10)在中文排序规则下一个汉字两字节十个字节能放五个汉字第六个就超了。解决是把字段改成NVARCHAR(20)按字符计长。改之前先查一遍现有数据的最大长度SELECT MAX(LEN(name)) FROM staff_info确认转换不会丢字。现象三个人信息页查不到刚入职的员工。原因是视图用了INNER JOIN串调动表没调动过的人天然被过滤掉调动过多次的人又会重复出现。解决是把「当前隶属」的数据源从调动表换成工作表调动视图单独建两者用途分开。验证方法很直接往员工表插一条不写调动记录的数据看视图里有没有。现象四部门工资汇总和财务手工算的对不上差几分钱。原因是金额字段用了FLOAT浮点累加会产生尾差。解决是改成DECIMAL(10,2)并在应用层做汇总时也统一用定点运算不要中途转成浮点再转回来。转换前先跑一遍差异比对确认历史数据的偏差在可接受范围内。现象五按月份统计考勤的查询越跑越慢。原因多半是WHERE YEAR(sign_date) ? AND MONTH(sign_date) ?这种写法字段被函数包住索引失效只能全表扫。解决是改写成日期区间或者建一个持久化计算列存年月再建索引。验证方法是看执行计划里有没有出现Index Scan出现就说明索引没用上。5.2 一条 SQL 自查数据的一致性设计阶段的自检不用写测试用例几条聚合查询就能把大部分结构性问题逼出来。-- 检查是否存在「同一员工同一时间归属两个部门」的脏数据 SELECT staff_id, COUNT(DISTINCT department_id) AS 部门数 FROM staff_work GROUP BY staff_id HAVING COUNT(DISTINCT department_id) 1; -- 检查调动记录的时间是否倒挂调入时间早于调离时间 SELECT * FROM staff_transfer WHERE new_time old_time; -- 检查员工表里的部门名与其部门编号对应的名称是否一致 SELECT s.staff_id, s.department_name, d.department_name FROM staff_info s JOIN department_info d ON d.department_id s.department_id WHERE s.department_name d.department_name;第一条能抓出主键设计不严的后果第二条抓时间字段的录入错误第三条专门用来验证「冗余字段是否已经开始腐烂」。这三条在部署前跑一遍比写一堆增删改查的测试有用。尤其是第三条如果查出来一堆不一致基本可以确认冗余字段这条路线要么加触发器要么彻底去掉。6. 设计取舍六张表背后的三个判断这套模型最核心的取舍是把「当前状态」和「历史轨迹」彻底分开。工作表存的是此刻这个人在哪个部门、什么职称调动表存的是变更流水。分开的代价是查「某人某天属于哪个部门」需要多一次按时间排序的查询收益是任何时点的组织架构都能被还原。反过来如果把部门编号直接写在员工表里图省事那么一次调动就会把历史覆盖掉按年统计调入调出这类需求永远做不出来。人事系统的价值有一半在追溯这个取舍没有第二选项。第二个判断是冗余字段的取舍。部门名称、姓名、工龄这些信息在多个表里重复出现严格按第三范式都该去掉。但报表查询动辄连四五张表性能和维护成本要平衡。我的经验是会随时间变化的冗余工龄、年龄坚决不留能算就算作为历史快照的冗余调动记录里的姓名可以留但要明确它是快照不跟着源表更新纯粹为了少写 JOIN 的冗余员工表里的部门名除非有强性能理由否则不留因为它制造了第二个真相来源。第三个判断是主键用代理键还是业务键。这一点上没什么可纠结的代理键加唯一约束永远比拿身份证号当主键省事。唯一约束保证了业务唯一性代理键保证了关联稳定两件事分开管后面改起来才有余地。要验证这套设计站不站得住不需要复杂工具。挑三个问题问自己就够了某个员工三个月前的部门能不能查到某个部门去年调出了几个人能不能算出来同一个人的工资汇总会不会因为重复行而翻倍三个问题都能用一条 SQL 答上来模型基本就是稳的。从那以后我每次动手建表之前都会强制走一遍「当前状态放哪张表、历史轨迹放哪张表」这个自问哪怕只是一个小 demo。很多看起来复杂的口径争议追到根上都是这两件事没分干净。希望帮到你。本文还有配套的精品资源点击获取