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

概述
一个事件数据库，其中有许多列保存查找表中记录的id。
我想解决的问题
我需要想出一个健壮的解决方案来管理历史数据，其中一些字段包含查找id。我列出了我提出的解决方案和备选方案。我想从其他开发人员那里了解他们是否在项目中以类似的方式管理这些场景。也许你有更好的方法？
数据库：Oracle 10g
栏：部门名称
场景：部门名称可以在一年中更改x次。业务部门需要报告其所有部门的数据，但希望看到事件以其各自部门的名称出现，就像事件发生时一样。
建议的解决方案：在部门名称查找表中设置条目时，请设置开始和结束日期值。使用视图，根据事件日期创建一个计算字段，以便在任何给定时间点访问正确的部门名称。
优点：通过一点防御性的编码，它可以让选定的用户通过一个gui来管理他们的静态数据，而不需要任何额外的数据库更改。可以随时更改，例如完全更改名称。不需要DBA支持。
缺点：考虑到在大型数据集上进行的查找/计算量，这可能是一个昂贵的操作。
替代解决方案：只需使用并插入部门名称的纯文本值。这里的缺点是DBA需要临时请求来更改/更新值，这可能是针对特定的日期范围，并且会错误地丢失一些记录。桌子空间的消耗也会增加。
列：分配的技师ID
场景：一个事件将指派一名技术人员存储技术人员的ID。查找表将包含所有可用技术人员的“当前”列表。当人们离开公司时，必须更新名单，并删除过时的技术人员。这是为了将下拉列表中的值数保持在最小值。业务部门仍希望了解在所有事故数据上分配了哪些技术人员。
解决方案：不要从技术人员查找表中删除条目，而是用表示“已存档/已删除”的标志标记该条目。此标志将充当gui下拉菜单上的筛选器，以删除不需要的条目。
优点：查找表只包含employee表中技术人员的uid。因此，如果业务需求发生变化，则很容易在主视图中呈现技术人员的任何属性，例如全名或员工编号等。
缺点：与前面的示例一样，查找可能是对大型数据集的昂贵操作。在gui方面，在业务逻辑和设计方面还需要额外的工作。特别是当原始条目已“存档”时如何管理下拉列表。
另一种解决方案：与上面的示例一样，只需使用纯文本值。这里的缺点是消耗更多的表空间，并且随着业务需求的变化而变得不灵活。

最佳答案

有一种称为版本控制的技术已经存在很多年了，但是由于几个原因，它在很大程度上是不可行的。然而，有一种类似的技术我称之为版本标准形式，我发现它非常有用。下面是一个使用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的组合必须是唯一的，因此对于同一个雇员，不能同时有两个版本处于活动状态，也不能在一个版本结束的时间和下一个版本开始的时间之间有间隔。
大多数查询都想知道员工数据的当前版本。这是通过将Employee的静态行与现在生效的版本相连接来提供的。这可以通过以下查询找到：

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()）中使用不等式别让子查询影响到你。所有的搜索字段都被索引，所以结果相当快。
这种设计有很大的灵活性。上面的查询返回当前和过去所有员工的最新数据。您可以检查TermDate字段以获取当前员工。事实上，由于应用程序中有很多地方只对当前员工的当前信息感兴趣，因此该查询将提供一个良好的视图（省略最后的where子句）。不需要应用程序甚至知道这样的版本存在。
如果您有一个特定的日期，并且希望看到当时有效的数据，那么将子查询中的v2.EffDate <= NOW()更改为v2.EffDate <= :DateOfInterest。
更多细节可以在幻灯片演示here和未完全完成的文档here中找到。
为了展示设计的一些可扩展性，请注意版本表中有一个IsWorking指示符，静态表中有一个终止日期。当员工离开公司时，最后一个日期将插入静态表中，并将IsWorking设置为false的最新版本的副本插入到版本表中。
员工离开一家公司一段时间然后再被录用是很常见的。只要静态表中有日期，就可以通过将该日期设置回空值来再次激活条目。但是，如果查询对象不再是雇员，则返回一个结果。没有迹象表明他们离开了公司。但是，如果一个版本在离开公司时IsWorking=false，在返回公司时IsWorking=true，则允许在感兴趣的时候检查该值，并且在员工不再是员工时忽略他们，即使他们稍后返回。

关于database - 如何最好地管理数据库中的历史查找值？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47478331/