php考勤系统数据表设计

Question

打卡签到功能这个表好设计，关键是怎么统计出来一个月应该签到多少天，正常签到多少天，迟到多少天，早退多少天，这样还需要增加什么表来设计好呢?

目前是用户一个表 ，签到一个表。
签到表存了签到时间和签退时间，目前是一天必须一次签到一次签退。
主要是统计这个月1号到当天正常签到多少天，迟到多少天，早退多少天，缺卡多少天（和具体的缺卡日期）。打卡的记录数据库都有这个好计算，关键是缺卡记录怎么存呢？

这个还需要考虑的星期天，这个是否要再加个字段来存一下签到的具体情况，这个字段如何设计呢？
效果如下：而且这个是实时统计的，也就是当月1号到当天的记录。

Answer 1

用户一张表（User），签到记录一张表（Sign），剩下全是算法

伪代码：

User(id: int primary key, username: string, sign: array, ...)

Sign(id: int primary key, user_id: int foreign key(User.id), action: bool, date: datetime, ...)

其中User.sign是一个与当月天数等长的短整型数组（可能要postgresql才支持，不支持数组的话可以用字符串存），表示这个用户当月签到情况，比如0表示未签到，1表示正常签到，2表示迟到但未早退，3表示未迟到但早退，4表示迟到且早退。Sign.action表示签到或签退（或者可以拆成两个表，签到表和签退表）。

每次签到的时候，更新这个用户的sign字段（算法根据需求自己编写）。

月末清空Sign表，有必要的话可以将这个月的签到记录保存到文件以供查证。

Answer 2

Sign:
    id: (uuid),
    user_id: (uuid),
    sign_date: (date),
    sign_time: (time),

统计,某个人,在某一天,签到的时间.

1. 判断某人迟到次数:
SELECT COUNT(DISTINCT sign_date) FROM Sign WHERE sign_time < ? AND user_id = ? AND sign_date >= ? AND sign_date <= ? GROUP BY sign_date;    
-- 问号为上班开始时间.这样的结果就是没有迟到的次数.减法后就是迟到次数

2. 判断某人早退次数:
SELECT COUNT(DISTINCT sign_date) FROM Sign WHERE sign_time > ? AND user_id = ? AND sign_date >= ? AND sign_date <= ? GROUP BY sign_date;    
-- 问号为正常下班时间.减法后,就是没有正常下班的记录.

3. 正常签到次数:
-- 每天签到2次以上,且存在早于上班开始时间, 晚于下班时间的签到时间
-- 这个如果用单个SQL 来实现的话,会比较复杂,且查询效率不高.所以推荐使用程序遍历查询.