如何最好地管理数据库中的历史查找值？

Question

概述

一个事件数据库，它将有许多列保存在查找表中的记录的ID。

--我想解决的问题--

我需要想出一个健壮的解决方案来管理历史数据，其中一些字段包含查找ID。我已经列出了我提出的解决方案和替代方案。我想从其他开发人员那里了解，他们在项目中是否以类似的方式管理这些场景。也许你有更好的方法？

数据库：Oracle10g

列：Department_name

场景：部门名称可以在一年中更改X次。该业务需要报告其所有部门的数据，但希望看到事件发生时以各自部门的名称命名。

建议的解决方案：当在部门名称查找表中设置条目时，设置一个开始日期和结束日期值。使用视图，创建基于事件日期的计算字段，以便在任何给定时间点访问正确的部门名称。

Pros：使用一些防御性编码，它将使所选用户的自助服务能够通过GUI管理他们的静态数据，而无需进行任何额外的数据库更改。可以随时进行更改，例如完全更改名称。不需要DBA支持。

Cons：考虑到在大型数据集上正在执行的查找/计算量，可能是一项昂贵的操作。

替代解决方案：只需使用并插入部门名称的纯文本值。这里的缺点是，对于更改/更新值的临时请求，需要DBA，可能是针对特定的日期范围，并且错误地丢失了一些记录。还将增加桌面空间的消耗。

列：Assigned_Technician_ID

场景：事故中将指定一名技术员，将在其中存储技术人员的ID。查找表将保存所有可用技术人员的“当前”列表。随着人们离开企业，名单必须更新和过时的技术人员删除。这是为了将下拉列表中的值数目保持在最小。企业仍然希望看到哪些技术人员被分配到他们所有的事故数据。

解决方案：与其从技术人员查找表中删除条目，不如用标记“归档/删除”的标志标记条目。此标志将充当GUI下拉列表上的筛选器，以删除不必要的条目。

Pros：查找表将只包含来自employee表的技术人员的UID。因此，如果业务需求发生变化，就很容易在主视图中呈现技术人员的任何属性，例如全名或员工编号等。

Cons：与前面的示例一样，查找在大型数据集上可能是一项昂贵的操作。在GUI方面还需要在业务逻辑和设计方面进行额外的工作。具体而言，当原始条目被“归档”时，如何管理下拉列表。

替代解决方案：与前面的示例一样，只需使用纯文本值即可。这里的缺点是消耗更多的表空间，并且随着业务需求的变化而变得不那么灵活。

Answer 1

有一种称为版本控制的技术已经存在很多年了，但是由于几个原因，它基本上是不可行的。然而，有一个类似的技术，我称之为版本范式，我发现这是非常有用的。下面是一个使用Employees表的示例。

首先，创建静态表。这是主实体表，它包含有关实体的静态数据。静态数据是指在实体生命周期内不会发生变化的数据，例如出生日期。

create table Employees(
  ID        int  auto_generated primary key,
  FirstName varchar( 32 ),
  Hiredate  date not null,
  TermDate  date,            -- last date worked
  Birthdate date,
  ...              -- other static data
);

重要的是要认识到每个员工都有一个条目，就像任何这样的表格一样。

然后是关联的版本表。这将与静态表建立一个1-m关系，因为一个雇员可能有几个版本。

create table Employee_versions(
  ID         int   not null,
  EffDate    date  not null,
  char( 1 )  IsWorking not null default true,
  LastName   varchar( 32 ),    -- because employees can change last name
  PayRate    currency not null,
  WorkDept   int   references Depts( ID ),
  ...,              -- other changable data
  constraint PK_EmployeeV primary key( ID, EffDate )
);

在版本表说明中，有一个有效日期，但没有匹配的“无更长有效”字段.这是因为一旦一个版本生效，它就会一直有效，直到被后续版本所取代。ID和EffDate的组合必须是唯一的，因此不能对同一员工同时活动，也不能在一个版本结束的时间和下一个版本开始的时间之间出现间隔。

大多数查询都希望知道员工数据的当前版本。这是通过将员工的静态行与现在有效的版本连接起来提供的。这可以通过以下查询找到：

select  ...
from    Employees e
join    Employee_versions v1
    on  v1.ID = e.ID
    and v1.EffDate =(
        select  Max( v2.EffDate )
        from    EmployeeVersions v2
        where   v2.ID = v1.ID
            and v2.EffDate <= NOW()
    )
where  e.ID = :EmpID;

这将返回最近开始的一个版本，也是唯一一个版本。在日期检查(v2.EffDate <= NOW())中使用不等式v2.EffDate <= NOW()允许将来的生效日期。假设您知道一个新员工将在下个月的第一天开始工作，或者计划在下个月十三日加薪，这些数据可以提前插入。这样的“预加载”条目将被忽略。

不要让子查询影响到您。所有的搜索字段都是索引的，所以结果非常快。

这种设计有很大的灵活性。上面的查询返回当前和过去所有员工的最新数据。您可以检查TermDate字段以获取现有员工。事实上，由于应用程序中有很多地方只对当前员工的当前信息感兴趣，所以该查询将提供一个很好的视图(省略最后的where子句)。应用程序甚至不需要知道这些版本的存在。

如果您有特定的日期，并且希望看到当时生效的数据，那么将子查询中的v2.EffDate <= NOW()更改为v2.EffDate <= :DateOfInterest。

更多细节可以在幻灯片演示文稿这里和未完全完成的文档这里中找到.

为了展示一下设计的可扩展性，请注意版本表中有一个IsWorking指示符，静态表中还有一个终止日期。员工离开公司时，最后一个日期将插入静态表中，并将IsWorking设置为false的最新版本的副本插入版本表中。

员工离开一家公司一段时间，然后再被录用，这是相当普遍的。使用静态表中的date，只要将日期设置为NULL，就可以再次激活该条目。但是，当这个人不再是雇员时，“回首”查询就会返回结果。没有迹象表明他们已经离开了公司。但是，在离开公司时使用IsWorking = false和返回公司时使用IsWorking = true的版本将允许在感兴趣时检查该值，并在员工不再是员工时忽略他们，即使他们稍后返回。

Answer 2

我是Server开发人员，经常遇到这些问题。除非我将数据加载到数据仓库中，否则我从来不喜欢使用文本(去角色化)。

Re: Department_name

有效日期可能是最好的答案，让我停下来的是我不确定我是否理解这个问题。我想不出有什么业务可以让一个部门这么频繁地更名。

Re: AssignedTech

我在几乎所有基于员工的查找中都使用了活动标志。我从来没有这样做过性能问题。在处理高周转公司时，我使用了过滤的索引和观点。