工作/项目工时数据库 - 编辑记录

Question

我整理了一个非常简单的工作/项目打卡/考勤表，供员工上下班。每行包含一个进孔和一个出孔。然而，有时员工忘记在适当的时间打卡或下班，所以我必须修改他们的打卡方式。

我的问题是：如何检查以确保我要做的更改不会与现有的打孔重叠？

编辑：

该表格看起来像这样：

Punch ID,Project Number,Employee ID,DateTime In,DateTimeOut
138,PA15212,1234,1/1/2010 1:00,1/1/2010 5:45
139,AD15217,5678,1/1/2010 1:00,1/1/2010 3:15
140,SL15222,4567,1/1/2010 1:00,1/1/2010 2:30
141,GA15151,5678,1/1/2010 3:20,1/1/2010 5:45

编辑2

为了澄清，正如我在下面的评论中所说，数据库是工作/项目时间跟踪系统。基本上，员工是进入工作岗位的。一旦他们这样做了，他们唯一的选择就是出局。然后他们就可以开始下一项工作……并且一天中会处理好几项工作。将输入和输出打孔放在同一个记录上是一个简单/不复杂的系统，可以轻松匹配输入和输出打孔以及计算该记录中的时间。然而，有时他们会忘记打卡上班，最终会迟到，或者忘记打卡下班。我需要修改这些拳头，但我想确保更改不会与该员工现有的进/出时间范围重叠。

Answer 1

我认为你试图将太多的东西合并到一张表中，这使得事情变得更加复杂。所提出的设计的一个主要问题是没有审计跟踪，因此如果出现计费或员工纠纷，则很难证明数据有效。

我建议使用真正的事务日志（仅限选择/插入），记录正在计算时间的人员和输入数据的人员，并结合派生您正在查找的摘要信息的数据视图为了。然后，您可以通过遍历日志查看相邻事件（“重击”）来检查冲突。

Answer 2

FWIW我认为你应该只捕获实际发生的情况，如果有人忘记打卡或打卡，那么应该没有相应的条目。

如果您对实际发生的情况进行建模，那么您始终可以根据应用程序中或通过存储过程中的业务规则动态创建丢失数据的默认值。这还允许您更改默认值，因为您总是计算它们。另外，它允许报告/统计数据等，说明何时发生这种情况，谁是犯罪者等。此外，重要的是，您的数据库代表了真正发生的事情，而不是一些捏造的东西。

PunchID, EmployeeID, ProjectNbr, Timestamp, Direction
111, 111, 101, 1/1/11 8:00, IN
111, 111, 101, 1/1/11 17:00, OUT

Answer 3

用于查找重叠记录的 SQL（技巧是将表与其自身连接起来）：

SELECT * 
  FROM TABLE_XXX x1, TABLE_XXX x2 
 WHERE x1.employee_id = x2.employee_id
   AND x1.DateTimeIn < x2.DateTimeIn
   AND x1.DateTimeOut > x2.DateTimeIn
   AND x1.DateTimeIn > ?  -- start of the day in question
   AND x1.DateTimeIn < ?  -- end of the day in question
   AND EMPLOYEE_ID = ?

我对设计的 2c：

1. 如果保留当前设计，您应该创建插入和更新触发器来检查重叠并在重叠时抛出异常 。

2. 仅存储 PerformanceDBA 建议的每个活动的开始时间。这意味着你必须有一个特殊的项目来打卡。您可以通过使用丢失的打孔时间进行插入来修复丢失的打孔。
   该解决方案还将确保除了故意的时间之外（其中活动是特殊的“打卡”活动），时间上不存在任何漏洞。
   恕我直言，这是最好的设计。

Answer 4

1. “表”存在几个问题，给您带来麻烦。

   • 缺失的一拳值多少钱？您是如何为打卡但未打卡下班的员工生成或默认该信息的？无效的 ？ 23:59 ?日期时间在 ?它们都是不正确的，但你必须有一些已知的值。）

   • 当您回答这个问题时，我们就可以编写 SQL 代码（这确实是一个 SQL 问题）。

   • 您希望用什么值来更新缺失的打卡时间：

   • 下次开始时间减 5 分钟

   • 哪里没有下次开始时间？ 17:00 ?
     .

2. 就AFA数据库设计而言，没有看到其他表，表应该如下。假设他们为每个项目（工作？）打卡和打卡。

   CREATE TABLE Punch (
       PunchID，--不知道它有什么用，但无论如何我都会保留它
       项目编号,
       员工 ID,
       日期时间，
       IsStart BIT -- 布尔值、tinyint
       )

   • 当 Punch IN 发生时，Punch OUT 尚未发生，因此您为此存储的任何值都是 false。

   • 丢失的行很容易识别

3. 前端、GUI、基于字符的命令等等，都需要一些改进。

   • 它应该明确地打入或打出

   • 当注册一个 Punch IN 时，如果没有之前的 Punch OUT，则可以使用一系列默认值，并首先创建一个 Punch OUT。例如当天：使用当前日期时间，第二天：使用前一天 17:00。

Answer 5

这是我对澄清你的问题的看法。评论一下我理解错误和遗漏的地方。我会努力不断修改。

您正在寻找针对“Punch”事件的两个异常的解决方案。您要涵盖的两个例外情况是：

1. 一名员工打孔了，但今天早上忘记打卡了

2. 一名员工打了孔昨天进来但忘记打卡了

   • 对于第一个例外，您可以用来创建逻辑的信息是当前的打卡时间。如果当前时间晚于（例如 12 点整），则填写今天 9 点的标准打卡时间。

   • 第二拳可以类似处理；如果当前时间已经是晚上 03 点多了，那么该员工昨天很可能忘记打卡下班了。

正如您可能看到的，我知道这个简单的逻辑可能不是您正在寻找的答案。但请评论我在这里错过的内容，我将对其进行修改以满足您的需求。

Answer 6

伪代码怎么样：

onPunchIn() {
if (DateTimeOut is null and now() < tomorrow()) { 
    Set DateTimeOut = now() 
    Create new record } 
if (DateTimeOut is null and now() > tomorrow()) {
    Set DateTimeOut = getDefaultEndOfDayFromYesterdafy() 
    Create new record }
}

Answer 7

首先，关于您的问题，从模式的角度解决此问题的方法是存储每行之前的超时时间。此设计的技巧是对 ProjectNumber、EmployeeId 和 DateTimeIn 设计唯一约束，并在 ProjectNumber、EmployeeId 和 PreviousDateTime 上对 ProjectNumber、EmployeeId、DateTimeOut 进行外键引用。还应该注意的是，我依赖于允许单个 null 的唯一约束，这在所有数据库系统中并非如此（当然，我也依赖于数据库系统遵守检查约束，这在所有数据库系统中也并非如此） ）。

Create Table Punches
    (
    PunchId int not null Primary Key
    , ProjectNumber varchar...
    , EmployeeId int not null..
    , DateTimeIn datetime not null
    , DateTimeOut datetime null
    , PreviousDateTimeOut datetime null

    , Unique ( ProjectNumber, EmployeeId, DateTimeIn )
    , Unique ( ProjectNumber, EmployeeId, DateTimeOut )
    , Unique ( ProjectNumber, EmployeeId, PreviousDateTimeOut )

    , Check ( DateTimeIn <= DateTimeOut )
    , Check( PreviousDateTimeOut <= DateTimeIn )

    , Foreign Key ( ProjectNumber, EmployeeId, PreviousDateTimeOut )
        References Punches( ProjectNumber, EmployeeId, DateTimeOut )
    )

这种方法的优点是模式本身可以防止重叠。缺点是插入有点棘手。给定项目的给定员工的第一行需要对 PreviousDateTimeOut 使用 null，因为我们没有前一行。其次，这意味着打卡需要找到之前的日期时间。

Insert Punches( ProjectNumber, EmployeeId, DateTimeIn, PreviousDateTimeOut )
Select ProjectNumber, EmployeeId, CURRENT_TIMESTAMP
    , Coalesce(
        (
        Select Max( DateTimeOut )
        From Punches
        Where DateTimeOut Not Null
        )
        , CURRENT_TIMESTAMP )

上面只是解决了重叠的问题。但是，除了通过防止插入同一 ProjectNumber 和 Employee 以及 null DateTimeOut 的两行来防止在没有先前打卡的情况下打卡之外，它不一定能解决忘记打卡的问题。应该发生的事情可能涉及更复杂的业务规则。

Answer 8

当您问：“我需要修改那里的冲孔，但我想确保更改不会重叠”时，解决方案是：（1）修改/添加冲孔 -> (2)验证-> (3)是否保存

一般情况下，如果没有“关闭”没有EndDate的员工，则不应为员工添加打卡。
然后->如果所有打孔都“关闭”，您可以添加新的打孔，起始日期大于员工的任何其他结束日期 - 但如果有一天员工忘记盖章（进出），您将不得不在其他人之间添加“新旧”打孔- 所以：您选择员工，设置您的开始日期和结束日期、项目 ID 等 ->然后您在数据库中检查是否存在以下情况：

1. 有 StartDates 或 EndDates
   在数据库中您的 StartDate 和
   结束日期 -->重叠（其他冲孔
   在新打孔内开始或结束）

2. 开始日期比您的要短
   StartDate 和 EndDate 大于
   您的结束日期 --> 重叠（您的新
   打孔器在其他打孔器内）

SQL 中的所有这些查询都必须为一名员工完成（一名员工不能同时参与多个项目）

如果没有出现这些错误，您可以安全地添加打孔。我对一些伪代码和我的英语感到抱歉。我希望它有点用处。