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

Question

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

databaseoracledatabase-design

3

概述

一个事件数据库，其中包含多个列，每个列都有一个ID来存储在查找表中的记录。

我要解决的问题

我需要想出一个强大的解决方案来管理历史数据，其中一些字段包含查找ID。我列出了我的建议解决方案以及备选方案。我想知道其他开发人员是否在其项目中以类似的方式管理这些情况。也许您有更好的方法？

数据库：Oracle 10g

列：Department_name（部门名称）

场景：部门名称可以在一年内更改X次。业务需要报告所有部门的数据，但希望按照事故发生时部门的相应名称查看事故。

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

优点：通过少量的防御性编码，它将使选定用户能够通过GUI管理其静态数据，而无需进行任何其他数据库更改。可以进行即时更改，例如完全更改名称。不需要DBA支持。

缺点：在大型数据集上执行的查找/计算量可能很大，因此成本较高。

备选方案：简单地使用并插入部门名称的纯文本值。这里的缺点是需要DBA进行即席请求以更改/更新值，可能会针对特定的日期范围，并且错误地遗漏某些记录。还将增加表空间消耗。

列：Assigned_Technician_ID（已分配技术员ID）

场景：一个事件将有一个被分配的技术人员，技术人员的ID将被存储。一个查找表将保存所有可用技术人员的“当前”列表。随着人员离开企业，必须刷新列表并删除过时的技术人员。这是为了将下拉列表中的值的数量保持最少。业务仍将想要看到哪些技术人员被分配在其所有事件数据上。

解决方案：而不是从技术人员查找表中删除条目，标记该条目的标志表示“已归档/已删除”。此标志将作为GUI下拉列表中的过滤器，以删除不需要的条目。

优点：查找表将仅包含来自员工表的技术人员UID。因此，如果业务要求发生变化，则可以轻松地在主视图中呈现技术人员的任何属性，例如全名或员工编号等。

缺点: 与前面的例子一样，在大型数据集上进行查找可能是一个昂贵的操作。在业务逻辑和设计方面，需要额外的工作来处理下拉列表，特别是当原始条目被“归档”时。

替代方案: 如上面的先前示例中所述，只需使用纯文本值即可。这里的缺点是占用更多的表空间，并且在业务需求发生变化时不够灵活。

- Sulphy

如果是Oracle 10g，为什么要添加所有这些其他标签，比如mysql、sql-server？ - Kaushik Nayak

嗨，Kaushik，它们是SO推荐的标签。从概念上讲，我提出的问题适用于任何数据库技术，而不仅限于Oracle。我本可以省略有关Oracle的要点，但我想在我的用例中包含它，以防有一些我不知道的内置功能对我有利。 - Sulphy

1

“large” - 数百万行？十亿行？Start_date和End_date通常难以优化。您提到了“VIEWs”; 是否有这样的_要求_而不是SELECTs？请注意实体属性值模式。规范化，但不要“过度规范化”。 - Rick James

嗨Rick。可能会有数千个，但最多可能是几百万，但需要一两年的时间才能达到这些数字。将有Qlikview报告工具来提取数据，因此希望通过使用中央视图使任何需要类似数据的工具变得更加容易。我不熟悉你提到的实体属性值模式，所以我会深入了解它：)。 - Sulphy

1

理想情况下，您需要支持SQL:2011 Temporal的数据库。https://en.wikipedia.org/wiki/SQL:2011 - Neil McGuigan

2个回答

1

我是一个SQL Server开发者，经常遇到这些问题。除非我正在将数据加载到数据仓库中，否则我从不喜欢使用文本（去规范化）。

关于Department_name：

有效日期可能是最好的答案，让我犹豫的是我不确定我是否理解了这个问题。我想不出部门频繁更改名称的业务案例。

关于AssignedTech：

我几乎在所有基于员工的查找中都使用活动标志。我从未因此产生过性能问题。当处理高人员流动率的公司时，我使用过过滤索引和视图。

- John

谢谢约翰！关于部门名称更改，我几年前为一家大公司做过一个项目，他们的业务资产类别通常每年至少合并和拆分一次。这只是这个特定公司的性质，可能不会在其他地方重复。我喜欢使用有效日期并删除结束日期的想法。 - Sulphy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- TommCatt · Accepted Answer

有一种技术叫做版本控制，已经存在了很多年，但由于几个原因而基本不可行。然而，我发现了一个类似的技术被称为“版本规范化形式”，我发现这个技术非常有用。下面是使用“员工表”进行示例。

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

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()) 允许未来的有效日期。假设您知道一个新员工将在下个月的第一天开始工作，或者加薪计划在下个月的13日进行，这些数据可以提前插入。这样的“预装载”条目将被忽略。

不要让子查询困扰你。所有搜索字段都已索引，因此结果非常快速。

这种设计具有很大的灵活性。上面的查询返回所有员工的最新数据，包括现在和过去的员工。您可以检查TermDate字段以获取当前员工。实际上，由于您的应用程序中的许多地方只对当前员工的当前信息感兴趣，因此该查询将成为一个良好的视图（省略最终的where子句）。应用程序甚至不需要知道这样的版本存在。

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

更多详细信息可以在幻灯片演示文稿这里和一个未完全完成的文档这里中找到。

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

员工经常离开公司一段时间后再次被雇用。只需在静态表中设置该日期为空即可重新激活该条目。但是，对于任何不再是员工的时间的“回顾”查询将返回结果。没有表明他们已离开公司的迹象。但是，当离开公司时IsWorking = false的版本，以及当重新回到公司时IsWorking = true的版本，将允许在感兴趣的时间检查该值并忽略员工，即使他们稍后返回。