将数据存储在可能在主表中更改的边表中 [英] Storing data in a side table that may change in its main table

问题描述

这是设计数据库时最佳实践的一个总体问题，我无法正确填写我的问题，在网上找到答案。

This is an overall question of best practice when designing a database and I have been unable to correctly word my question to find an answer on the net.

我有一个持有员工安全培训信息的数据库。关于需求的信息需要存储以进行审计，但是每年的需求可能会改变。我还需要记录一个员工需要做什么以及他们完成了多少工作。

I have a database that is holding information about employee's safety training. The information about requirements needs to be stored for auditing purposes, but year to year the requirements may change. I still need to keep a record of what an employee was required to do and how much of it they completed.

我的第一个反应是创建历史数据表，并且每次员工完成操作或完成操作所需的时间到达时基本上都存储一个快照这样，如果要求改变了道路上的岁月，那么历史表将不会指向错误的信息。

My first reaction was to create a table for historical data and store basically a "snapshot" every time an employee completes an action or the time required to complete the action expires, that way if the requirement changes years down the road the historical table won't be pointing at the wrong information.

我在实现时改变了这一点，并已经设置了应用程序它不会更改现有要求，它会在每次需求变化时创建一个新的需求，以便旧的需求仍然在数据库中并由历史表指向。在主表中，要求表将旧要求标记为非活动状态，永远不能通过应用程序删除。

I altered this upon implementation and have set up the application so that it doesn't change existing requirements it creates a new one every time the requirements change so that the old requirement is still in the database and pointed to by the historical table. On the main table the requirement table the old requirement is marked as inactive and can never be deleted through the application.

我确信这个问题以前遇到过，但我有无法很好地表达自己的能力来搜索可行的答案。我的问题是在这种情况下最好的方法是什么？我目前的实施是好吗？我的第一反应是否更好？还有另一种方式比两者都好吗？

I am sure this problem has been encountered before but I have been unable to articulate it well enough to search viable answers on my own. My question is what is the best way to proceed in this situation? Is my current implementation a good one? Was my first reaction better? Is there another way that is better than both?

在旁注中，如果有这种类型的问题的单词或短语（存储冗余数据，存储历史数据，存储审计数据，我不是确定所谓的它）请让我知道。

On a side note if there is a word or phrase for this type of problem (the storing of redundant data, storing historical data, storing auditing data, I'm not sure what to call it) please let me know.

推荐答案

据我所知，你有一个员工和相关的任务。我将为员工创建一个表，另一个为任务创建一个表。另外许多到许多表将链接到这些。在任务表中，您将具有从/到列生效的状态和日期。如果任务当前有效，您可以将有效期限保留为null或将值设置为1/1/2999。当查询任务时，您需要提供一个日期，以便您能够在特定日期之前使任务生效。如果你决定使用空值，那么where子句就像

As far as I understand you have an employee and associated tasks. I would create one table for employees and one for the tasks. Another many to many table will link these to. In the task table you will have a status and date effective from/to columns. if the task is currently effective you can leave the effective to date null or set the value to 1/1/2999. When querying the tasks you will need to provide a date so you will be able to get the task effective as of a specific date. If you decide to use null value the where clause will be like

其中t.DateEffectiveFrm <= @ AsOfDate和（@AsOfDate< t.DateEffetiveTo或t.DateEffetiveTo是null）

where t.DateEffectiveFrm<=@AsOfDate and (@AsOfDate < t.DateEffetiveTo or t.DateEffetiveTo is null)

如果对于当前有效的任务，您在远期将使用日期值（1/1/2999），where子句将变得简单
其中t.DateEffectiveFrm <= @ AsOfDate和@AsOfDate< t.DateEffetiveTo

if for currently effective tasks you use a date value in the far future ( 1/1/2999) the where clause will be even simple where t.DateEffectiveFrm<=@AsOfDate and @AsOfDate < t.DateEffetiveTo

您还可以考虑添加一个状态表，这将保留状态更改的历史记录

You might consider also to add a status table , which will keep a history of the status changes

这篇关于将数据存储在可能在主表中更改的边表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！