数据库PK-FK设计用于未来有效的日期条目？ [英] Database PK-FK design for future-effective-date entries?

问题描述

最终我将把它转换成Hibernate / JPA设计。但我想从纯粹的数据库角度出发。我们有各种包含未来生效日期的数据表。用以下伪定义来获取员工表：

员工

• id INT AUTO_INCREMENT


• ...数据字段...


• effectiveFrom DATE


• effectiveTo DATE

employee_reviews

• id INT AUTO_INCREMENT


• employee_id INT FK employee.id

非常简单。但假设员工A的id = 1，effectiveFrom = 2011/1/1，effectiveTo = 1/1/2099。该员工未来将会改变工作，理论上会创建一个新的行，id = 2 with effectiveFrom = 7/1/2011，effectiveTo = 1/1/2099，id = 1的effectiveTo被更新为6 /二千零十一分之三十零。但现在，我的程序必须通过任何与雇员每晚都有FK关系的表，并更新这些FK以引用新的有效员工条目。

我已经在纯SQL和Hibernate论坛上看到过不同的贴子，我应该有一个单独的employee_versions表，这是我将所有有效日期的数据存储在其中，导致下面更新的伪定义：

员工

• id INT AUTO_INCREMENT





employee_versions






• id INT AUTO_INCREMENT


• employee_id INT FK employee.id


• ...数据字段...


• effectiveFrom DATE


• effectiveTo DATE


$ b employee_reviews




• id INT AUTO_INCREMENT


• employee_id INT FK employee.id






然后要获取任何实际的数据，必须从employee_ver拥有正确的employee_id和日期范围。这对于每个版本化的实体都有这个次版本的版本表，感觉相当不自然。





任何人有任何意见，您以前的工作建议等等。就像我所说的，我首先从一个通用的SQL设计角度出发，然后再在Hibernate的顶层进行分层。谢谢！

解决方案




这位员工将来会改变工作，理论上创建一个新的（员工）行




为什么？这是什么意思？您的员工实体不再代表员工，它现在代表了处于某个职位的人的抽象概念。





我认为将员工更换工作时所发生变化的实体 - 职位 - 分离出来放在单独的表格中会更有意义，因此您不需要最终会出现一些混乱的概念，其中一个自然人实际上是多个 employee 行。





我不明白为什么你认为这看起来不自然，不得不从额外的表格中选择 - 你将分离出一些具有多重性（一个人的位置）与单数（雇员）的东西。




Ultimately I'm going to convert this into a Hibernate/JPA design. But I wanted to start out from purely a database perspective. We have various tables containing data that is future-effective-dated. Take an employee table with the following pseudo-definition:

employee

• id INT AUTO_INCREMENT
• ... data fields ...
• effectiveFrom DATE
• effectiveTo DATE

employee_reviews

• id INT AUTO_INCREMENT
• employee_id INT FK employee.id

Very simplistic. But let's say Employee A has id = 1, effectiveFrom = 1/1/2011, effectiveTo = 1/1/2099. That employee is going to be changing jobs in the future, which would in theory create a new row, id = 2 with effectiveFrom = 7/1/2011, effectiveTo = 1/1/2099, and id = 1's effectiveTo updated to 6/30/2011. But now, my program would have to go through any table that has a FK relationship to employee every night, and update those FK to reference the newly-effective employee entry.

I have seen various postings in both pure SQL and Hibernate forums that I should have a separate employee_versions table, which is where I would have all effective-dated data stored, resulting in the updated pseudo-definition below:

employee

• id INT AUTO_INCREMENT

employee_versions

• id INT AUTO_INCREMENT
• employee_id INT FK employee.id
• ... data fields ...
• effectiveFrom DATE
• effectiveTo DATE

employee_reviews

• id INT AUTO_INCREMENT
• employee_id INT FK employee.id

Then to get any actual data, one would have to actually select from employee_versions with the proper employee_id and date range. This feels rather unnatural to have this secondary "versions" table for each versioned entity.

Anyone have any opinions, suggestions from your own prior work, etc? Like I said, I'm taking this purely from a general SQL design standpoint first before layering in Hibernate on top. Thanks!

解决方案

That employee is going to be changing jobs in the future, which would in theory create a new (employee) row

Why? What is the point of this? Your employee entity no longer represents an employee, it represents now some abstact concept of "a person in a position".

I believe it would make more sense to separate out the entity that is changing when the employee "changes jobs" - the position - into a separate table, so you do not end up with some messy concept where one physical person is actually multiple employee rows.

I don't understand why you think this seems "unnatural" to have to select from the extra table - you would be separating out something that has multiplicity (a person's position) from something that is singular (an employee).

这篇关于数据库PK-FK设计用于未来有效的日期条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！