简介一份面向考勤管理系统开发的数据库设计文档适合数据库课程设计、毕业设计或企业人事考勤模块开发参考。文档围绕员工考勤管理核心业务给出员工基本信息表、部门信息表、考勤类型信息表、员工考勤信息表、用户信息表共五张核心表的逻辑结构对每张表的字段名、数据类型、主外键约束做了明确标注同时梳理了系统登录、用户管理、员工与部门维护、考勤记录增删改查、按员工/考勤类型/时间段组合查询以及按月按部门统计考勤次数与罚金等九类功能需求并提供考勤统计付表样例。通过这份资料读者可以快速理解数据库规范化设计、主外键关联和查询统计逻辑直接参考表结构或在此基础上进行二次开发。资源量为1个doc文档压缩包仅57KB轻量便于查阅。已有262人学习/下载是系统掌握考勤数据库设计思路的实用资料。1. 员工考勤管理系统的数据库设计难在月末统计不在建表员工考勤管理系统的功能需求看起来极度简单记录员工是谁、哪天迟到、扣多少钱最多加上部门维度。但把这个需求落到数据库设计文档里真正有信息量的部分是月末那两张交叉统计表——按月按部门统计每类考勤次数、按员工汇总各类罚金小计与总计。只要事实表字段设计错一个冗余、外键类型差一个精度统计 SQL 就会退化成只能手工改列的泥潭。这份设计文档给出了一个相对完整的边界五张基础表部门、员工、考勤类型、考勤事实、系统用户加九条功能需求主键外键和查询维度基本齐备到了可以直接建库的程度。同时文档里也留下了一些典型问题比如密码字段只有 10 位、事实表用check_name冗余考勤类型名。下面顺着这套设计从建表 SQL 开始复现再逐个打通组合查询、交叉统计和权限修正最后用一条数据一致性校验 SQL 收尾。2. 考勤表结构拆解book_info 到 emp_work_check_info 的主外键设计2.1 员工基本信息表 book_info表名从哪来字段要怎么改这份文档里员工表叫book_info一眼就能看出是从图书管理系统模板里带过来的。线上项目建议改成employee_info不过表名不影响下面的结构分析。核心字段如下字段类型约束说明emp_novarchar(10)主键员工编号empNamevarchar(20)非空员工姓名dep_novarchar(10)外键 → dep_info所属部门birthdaydatetime可空出生日期addressvarchar(40)可空住址hire_datedatetime可空雇佣日期telvarchar(20)可空联系电话photeimage可空员工照片emp_no用varchar(10)是合理的员工编号通常由前缀加序号组成比如EMP000001定宽数字落库反而更麻烦。phote image是 SQL Server 的旧类型MySQL 里对应LONGBLOB。实际开发中我一般不在表里存照片二进制而是把证件照上传到 OSS 后存 URL否则一个几千人的公司照片字段会把备份体积和查询 IO 同时拖垮。dep_no在员工表里是varchar(10)在部门表里却是char(3)这是整份设计里第一个硬伤。Join 时 SQL Server 会把varchar列隐式转换为char或反过来一旦隐式转换落在索引列上emp_no、dep_no上的索引就无法参与 Seek部门连接查询只能走扫描。建表时应该把两边的dep_no统一为同一种类型推荐char(3)。2.2 部门信息表 dep_infochar(3) 的语义是对的部门表dep_info原文这样命名正文里写作 dep_info只有两个字段dep_no char(3)主键depName varchar(10)部门名。char(3)在只有三位固定部门编号的场景下是对的存001、002这种值不会产生碎片。但depName varchar(10)在 SQL Server 里按字节数统计10 个字节只能存 5 个汉字“技术研发部”这种 5 个字的名字刚好到边界再来个“产品与用户运营部”直接溢出。MySQL 的varchar(10)按字符数计算可以存 10 个汉字。所以跨库移植这份设计时varchar长度的定义是第一个要重新核对的地方。2.3 考勤类型表 work_check_categories罚金精度是坑考勤类型表独立一张表放字典数据设计思路没有问题check_no varchar(20)主键类型编号check_name varchar(20)考勤类型名事假、病假、迟到、缺席、早退fine numeric(3)罚金numeric(3)表示精度 3、小数位 0最大只能存 999而且存不了 1.5 元这种带角分的罚金。如果公司规定迟到一次罚 50、旷工一天罚 200这个字段是够用的但只要出现 0.5 天按比例折算numeric(3)就会报溢出。建议直接改成numeric(8,2)成本为零后路多一条。2.4 考勤事实表 emp_work_check_info主键和冗余字段都要推敲员工考勤信息表是整份设计的事实表字段包括bh编号、emp_no、check_date日期、check_name考勤类型、fine罚金。摘要里说主键是(emp_no, check_date)这个主键在线下系统里能跑但有一个业务漏洞如果员工同一天既迟到又早退需要插入两条记录时复合主键直接冲突。我一般这样处理bh作为自增主键SQL Server 用IDENTITY(1,1)MySQL 用AUTO_INCREMENT同时把(emp_no, check_date, check_name)设成唯一约束。这样一天多条考勤记录可以被正确保存重复插入同类型记录时数据库也会直接拒绝。另一个问题是事实表里直接存了check_name和fine。fine是从类型表冗余过来的快照值月度罚金统计时如果考勤类型表里的罚金日后被修改历史统计结果不会跟着变这是刻意为之。而check_name冗余则意味着考勤类型改名会牵连全部历史记录正确做法是事实表存check_no需要类型名时再 Join 类型表。文档里没提外键实际建表时至少应该给emp_no加外键引用员工表。2.5 五张表的建表 SQLSQL Server 风格下面的建表语句按原文档字段保留同时做了三处修正员工表dep_no改为char(3)、事实表主键用bh、罚金字段提到numeric(8,2)。CREATE TABLE dbo.dep_info ( dep_no char(3) NOT NULL, depName varchar(30) NOT NULL, CONSTRAINT PK_dep_info PRIMARY KEY (dep_no) ); CREATE TABLE dbo.book_info ( emp_no varchar(10) NOT NULL, empName varchar(20) NOT NULL, dep_no char(3) NOT NULL, birthday datetime NULL, address varchar(50) NULL, hire_date datetime NULL, tel varchar(20) NULL, CONSTRAINT PK_book_info PRIMARY KEY (emp_no), CONSTRAINT FK_book_info_dep FOREIGN KEY (dep_no) REFERENCES dbo.dep_info(dep_no) ); CREATE TABLE dbo.work_check_categories ( check_no varchar(20) NOT NULL, check_name varchar(20) NOT NULL, fine numeric(8,2) NOT NULL DEFAULT 0, CONSTRAINT PK_work_check_categories PRIMARY KEY (check_no) ); CREATE TABLE dbo.emp_work_check_info ( bh int IDENTITY(1,1) NOT NULL, emp_no varchar(10) NOT NULL, check_date datetime NOT NULL, check_name varchar(20) NOT NULL, fine numeric(8,2) NOT NULL DEFAULT 0, CONSTRAINT PK_emp_work_check PRIMARY KEY (bh), CONSTRAINT UQ_emp_check_date UNIQUE (emp_no, check_date, check_name), CONSTRAINT FK_emp_work_check_emp FOREIGN KEY (emp_no) REFERENCES dbo.book_info(emp_no) ); CREATE TABLE dbo.users ( bh varchar(10) NOT NULL, username varchar(20) NOT NULL, user_pwd varchar(10) NOT NULL, CONSTRAINT PK_users PRIMARY KEY (bh) );这段 SQL 里最关键的是UQ_emp_check_date唯一约束。它保留了摘要里(emp_no, check_date)作为业务键的语义同时放开了同一天多条不同类型记录的可能。bh的IDENTITY只负责保证物理主键唯一不参与业务判断。work_check_categories主键是check_no但事实表仍然存check_name这是为了贴近原文档描述如果按 2.4 的建议改为存check_no只需要把emp_work_check_info里的字段换掉查询时多一个 Join。3. 考勤查询需求拆解模糊查询、时间区间与组合查询的 SQL 实现3.1 员工基本信息的三类查询系统功能需求第 4 条写了三种员工查询方式按姓名模糊查询、按部门查询、按雇佣时间段查询。注意原文第二条写的是“按读者名模糊查询”这是从图书系统模板复制过来的笔误实际应该是员工名或员工姓名。-- 按姓名模糊查询参数化写法 DECLARE kw varchar(20) N张; SELECT b.emp_no, b.empName, d.depName, b.hire_date, b.tel FROM dbo.book_info b LEFT JOIN dbo.dep_info d ON b.dep_no d.dep_no WHERE b.empName LIKE N% kw N%; -- 按部门查询 SELECT b.emp_no, b.empName, d.depName FROM dbo.book_info b JOIN dbo.dep_info d ON b.dep_no d.dep_no WHERE d.dep_no 003; -- 按雇佣时间段查询避免 BETWEEN 的边界问题 SELECT b.emp_no, b.empName, b.hire_date FROM dbo.book_info b WHERE b.hire_date 2023-01-01 AND b.hire_date 2024-01-01;模糊查询用%加关键字拼接注意中文前面加N前缀避免常量字符串按非 Unicode 隐式转换影响索引比较。部门查询走的是dep_no外键如果员工表里的dep_no保持char(3)这里的003才能和索引列类型完全匹配。时间段查询尽量用和表达左闭右开区间BETWEEN在datetime类型上会漏掉当天 23:59:59 以后的记录这个问题在第四章月度统计里还会出现。3.2 考勤信息组合查询一个存储过程覆盖全部组合需求第 8 条要求按员工、考勤类型、时间段分别查询同时支持三者任意组合。最省事的做法不是在前端拼六个 IF 分支而是写一个参数可空的存储过程用param IS NULL OR 列 param这种模式。CREATE PROCEDURE dbo.usp_QueryEmpWorkCheck emp_no varchar(10) NULL, check_name varchar(20) NULL, begin_date datetime NULL, end_date datetime NULL AS BEGIN SET NOCOUNT ON; SELECT e.bh, e.emp_no, b.empName, d.depName, e.check_date, e.check_name, e.fine FROM dbo.emp_work_check_info e JOIN dbo.book_info b ON e.emp_no b.emp_no LEFT JOIN dbo.dep_info d ON b.dep_no d.dep_no WHERE (emp_no IS NULL OR e.emp_no emp_no) AND (check_name IS NULL OR e.check_name check_name) AND (begin_date IS NULL OR e.check_date begin_date) AND (end_date IS NULL OR e.check_date DATEADD(DAY, 1, end_date)) ORDER BY e.check_date, e.emp_no; END;参数说明四个入参全部允许为NULL调用方只传需要过滤的维度其余不传。end_date用DATEADD(DAY, 1, end_date)而不是是为了覆盖end_date当天 23:59:59 之后的考勤记录这也是处理datetime类型最稳的写法。需要注意OR参数化查询在数据量大时可能出现参数嗅探导致执行计划不稳定生产环境可以给存储过程加WITH RECOMPILE或者在WHERE条件后追加OPTION (RECOMPILE)。几千人规模的小系统可以不用管但上了万级的考勤流水就必须考虑。3.3 验证查询有没有走索引写完存储过程后用下面两句话验证执行计划SET STATISTICS IO ON; SET STATISTICS TIME ON; EXEC dbo.usp_QueryEmpWorkCheck emp_no EMP000001;重点看输出里的Scan count和logical reads。logical reads突然涨到几万且执行计划里出现Table Scan或RID Lookup说明缺失索引。把鼠标放在执行计划上看 Missing Index 提示给出的字段一般就是下一章要建的复合索引。这在 PowerDesigner 画物理模型时看不出来只有落到真库上测过才知道哪里要补索引。4. 月度报表的交叉统计付表一和付表二的 SQL 写法4.1 付表一按部门按月的考勤次数矩阵需求里的付表一是这样的行是部门列是考勤类型事假、病假、迟到、缺席、早退值是当月该部门各类考勤的发生次数。这类统计的标准写法是CASE WHEN加SUM不要用COUNT。DECLARE ym char(7) 2025-06; SELECT d.depName, SUM(CASE WHEN e.check_name N事假 THEN 1 ELSE 0 END) AS 事假, SUM(CASE WHEN e.check_name N病假 THEN 1 ELSE 0 END) AS 病假, SUM(CASE WHEN e.check_name N迟到 THEN 1 ELSE 0 END) AS 迟到, SUM(CASE WHEN e.check_name N缺席 THEN 1 ELSE 0 END) AS 缺席, SUM(CASE WHEN e.check_name N早退 THEN 1 ELSE 0 END) AS 早退 FROM dbo.emp_work_check_info e JOIN dbo.book_info b ON e.emp_no b.emp_no JOIN dbo.dep_info d ON b.dep_no d.dep_no WHERE e.check_date ym -01 AND e.check_date DATEADD(MONTH, 1, ym -01) GROUP BY d.dep_no, d.depName ORDER BY d.dep_no;这里用SUM(CASE WHEN ... THEN 1 ELSE 0 END)而不是COUNT(CASE WHEN ... THEN 1 END)原因在于COUNT只对非NULL值计数CASE WHEN不匹配时返回NULL那么某部门当月没有“迟到”时会得到 0 行而不是 0 值SUM对不匹配行返回 0统计结果更贴近报表预期。月份参数用char(7)拼接WHERE用左闭右开区间正好把 3.1 里说的边界问题挡在门外。部门维度一定要先按dep_no分组再加depNamedepName在部门表上不保证唯一只按名字分组有合并风险。4.2 付表二按员工统计当月各类罚金小计与总计付表二的行是员工列是各类考勤罚金最后一列是合计。此时fine应该用事实表里冗余的那一份而不是 Join 类型表重新取值否则后续调整迟到罚金会把历史月份报表全部改掉。DECLARE ym char(7) 2025-06; SELECT b.emp_no, b.empName, d.depName, COALESCE(SUM(CASE WHEN e.check_name N事假 THEN e.fine END), 0) AS 事假罚金, COALESCE(SUM(CASE WHEN e.check_name N病假 THEN e.fine END), 0) AS 病假罚金, COALESCE(SUM(CASE WHEN e.check_name N迟到 THEN e.fine END), 0) AS 迟到罚金, COALESCE(SUM(CASE WHEN e.check_name N缺席 THEN e.fine END), 0) AS 缺席罚金, COALESCE(SUM(CASE WHEN e.check_name N早退 THEN e.fine END), 0) AS 早退罚金, COALESCE(SUM(e.fine), 0) AS 罚金总计 FROM dbo.book_info b LEFT JOIN dbo.dep_info d ON b.dep_no d.dep_no LEFT JOIN dbo.emp_work_check_info e ON e.emp_no b.emp_no AND e.check_date ym -01 AND e.check_date DATEADD(MONTH, 1, ym -01) GROUP BY b.emp_no, b.empName, d.depName ORDER BY d.depName, b.emp_no;这段 SQL 有两点和直觉相反第一员工表在左边用LEFT JOIN考勤流水这样当月没有考勤记录的员工也会出现在报表里否则两个月没上班的员工会直接消失第二SUM(CASE WHEN ... THEN e.fine END)不匹配时得到NULL必须用COALESCE包成 0否则前端渲染会出现空白单元格。罚金总计直接SUM(e.fine)和左边各类小计的COALESCE互相独立原因在于SUM会跳过NULL只对实际存在的考勤记录求和。4.3 行列转置的选型CASE WHEN 够用就别上 PIVOT上面两段 SQL 都是固定列转置覆盖需求里列出的五类考勤没有问题。但考勤类型存在work_check_categories表里意味着它可以被增删今天加了“漏打卡”报表就要改 SQL。SQL Server 的PIVOT可以少写几个CASESELECT d.depName, [事假], [病假], [迟到], [缺席], [早退] FROM ( SELECT d.depName, e.check_name FROM dbo.emp_work_check_info e JOIN dbo.book_info b ON e.emp_no b.emp_no JOIN dbo.dep_info d ON b.dep_no d.dep_no WHERE e.check_date 2025-06-01 AND e.check_date 2025-07-01 ) src PIVOT ( COUNT(check_name) FOR check_name IN ([事假], [病假], [迟到], [缺席], [早退]) ) pvt;PIVOT的问题在于透视列必须写死考勤类型动态增减时它一样要改而且改起来比CASE WHEN更绕。真正的动态列方案要拼动态 SQL用STUFF把work_check_categories里的check_name拼进列清单这里不再展开。对这种数据量小、列基本固定的报表CASE WHEN是维护成本最低的选择。5. 用户权限字段改造、索引设计与数据一致性校验5.1 用户表要能支撑“超级用户”这个需求需求第 2 条要求支持超级用户但users表里只有bh、username、user_pwd三个字段完全无法区分普通用户和超级用户。最直接的修正是在建表后补一个角色字段同时把密码字段加长ALTER TABLE dbo.users ADD user_role tinyint NOT NULL DEFAULT 0; ALTER TABLE dbo.users ALTER COLUMN user_pwd varchar(64) NOT NULL;user_role用tinyint存 0 或 10 表示普通用户1 表示超级用户后面要加“部门考勤员”这种中间角色tinyint也够用。user_pwd原设计是varchar(10)这个长度连 MD5 的 32 位十六进制字符串都放不下更别说 SHA-256。实际项目里密码应该存哈希值而不是明文建议SHA-256或bcrypt的结果都按varchar(64)存。登录验证时先按username查出user_pwd和user_role再在内存里比对哈希不要把密码塞进查询条件。5.2 三张常用索引考勤系统的查询热点集中在emp_work_check_info上月度统计和组合查询都绕不开emp_no、check_date两个字段。文档没有给索引设计实际建库时至少补下面三条CREATE INDEX IX_work_check_emp_date ON dbo.emp_work_check_info(emp_no, check_date); CREATE INDEX IX_work_check_date ON dbo.emp_work_check_info(check_date) INCLUDE (emp_no, check_name, fine); CREATE INDEX IX_book_info_dep ON dbo.book_info(dep_no);第一条(emp_no, check_date)复合索引直接服务“按员工查时间段”的查询员工号等值匹配后日期范围可以用索引区间定位。第二条单列check_date索引给月度报表用INCLUDE把emp_no、check_name、fine带进索引叶级查询不需要回表。第三条book_info.dep_no索引很重要SQL Server 不会自动给外键列建索引部门表连接员工表时没有这条索引每次都是全表扫描。MySQL 的 InnoDB 会自动创建外键索引这份设计如果跑在 MySQL 上这条可以省略。5.3 上线前跑一遍数据一致性校验建完表和索引后用一条左连接查悬空引用是最快的验库方式SELECT e.emp_no, e.check_date, e.check_name FROM dbo.emp_work_check_info e LEFT JOIN dbo.book_info b ON e.emp_no b.emp_no WHERE b.emp_no IS NULL;正常结果应该是空集。如果查出记录说明考勤流水里有员工已经被删除但考勤记录还在要么先删流水再删员工要么把外键改为ON DELETE CASCADE二选一不要两个都做。同一思路可以套到book_info.dep_no上检查有没有员工挂在已经不存在的部门编号上这类脏数据比 SQL 写错更隐蔽而且一旦进入月度报表需要返工的就是整张 Excel。本文还有配套的精品资源点击获取