mysql - 如何在日期链表上创建唯一键

Question

我有两个表:employee 和一个子表employeeVacation。

员工

id
employeeName

员工假期

employeeId
vacationStartDate
vacationEndDate

employee 保存员工列表，并在 employeeName 列上有一个唯一的键。employeeVacation 保存每个员工的假期日期的详细信息。它在 employeeId 上有一个到 employee.id 列的foregin 键。此表中的每个employeeId 可以有多行。它没有唯一的 key 。

是否有更好的方法来构建这两个表，以便可以在 employeeVacation 表(或等效表)上创建唯一键？我正在寻找防止日期重叠的独特 key 或结构更改。

我想避免的这种重叠的示例:

第 1 行

employeeId =1
startDate = 2015-01-01
endDate = 2015-02-15

第 2 行

employeeId =1
startDate = 2015-02-10
endDate = 2015-02-20

我能想到的最好的办法是在 employeeId 和 startDate 上使用唯一的键。

多年来我一直在摸索，但仍然想不出一个，所以非常感谢任何帮助!

Answer 1

您无法使用给定的数据模型加上一些唯一的约束来解决这个问题，因为范围是唯一的，但这并没有说明它们是否重叠。

有两种可能的解决方案:

1. 您可以编写一个 BEFORE INSERT 触发器(也可能是更新前触发器)来代替唯一约束，该触发器使用查询来检查是否存在重叠记录，以便在以防万一的情况下引发异常。取决于dbms是否支持该功能。

2. 您可以使模型更加复杂，使其能够处理数天。那么“唯一”确实意味着“不重叠”。

主键粗体:

• 员工(员工号，姓名)
• 假期(员工号、假期号、状态)
• 假期(员工号、假期号、日期)

您也许可以不使用连接假期表:

• 员工(员工号，姓名)
• 假期(empno，天)

或者使用基于 ID 的数据库(附加唯一键斜体):

• 员工(id_employee、empno、姓名)
• 假期(id_vacationday、天、id_employee)

使用基于 ID 的数据库和方便的假期表，如果您希望 dbms 保证一致性(基于 ID 的系统通常就是这种情况)，您可能必须引入冗余:

• 员工(id_employee、empno、姓名)
• 假期(id_vacation、id_employee、状态)
• 假期(id_vacationday、id_vacation、day、id_employee)

你看，你仍然需要 id_employee 在假期中，尽管它是通过假期的引用隐式说明的。但是，某些 dbms 允许您对 View 有唯一约束，因此您可以加入假期和假期，并对 (vacationday.day, Vacation.id_employee) 有唯一约束，这样您就可以摆脱冗余问题。