参考数据的删除/失效方法

发布于 2024-10-24 16:11:00 字数 1385 浏览 4 评论 0原文

根据我在这里找到的讨论：数据库：删除或不删除记录，我想特别关注参考数据，对此添加一些想法，并询问您一般首选的方法，或者根据您决定采用哪种可用方法的标准。

（电话、邮件、传真……；我们的“我想主要关注的参考数据表”）：

Request (ID, Text, Channel_ID)
Channel(ID, Description)

让我们假设客户的“请求数据库”具有以下数据结构，而请求可以通过各种渠道传递首先，假设这两个表中有以下数据：

请求：

ID    | Text                                         | Channel_ID
===============================================================  
1     | How much is product A currently?             | 1 
2     | What about my inquiry from 2011/02/13?       | 1
3     | Did you receive my payment from 2011/03/04?  | 2

频道：

ID    | Description
===============================================================  
1     | Phone
2     | Mail
3     | Fax

那么，假设满足以下要求，您如何解决这个问题：

频道可能会随着时间的推移而改变。这意味着：他们的描述可能会改变。可以添加新的，仅从某些特定数据开始有效。频道可能会失效（到某个特定日期）
为了报告和监控的目的，需要能够识别最初使用哪个通道提交请求。
对于新请求，仅应允许当前“有效”的频道，而对于先前存在的请求，还应允许在该特定日期有效的频道。

根据我的理解，这显然需要一种超越删除标志的更丰富的无效方法，可能需要结合参考数据表的“ValidFrom / ValidTo”方法。

另一方面，这在请求数据捕获过程中遇到了一些困难，因为对于新请求，您只显示它们当前可用的通道，而为了维护预先存在的通道，需要显示创建此记录时所有可用的通道。显示。这不仅从开发的角度来看可能很复杂，而且对于用户来说也可能不直观。

您通常如何为可能随时间变化的参考数据设置数据模型？那么你如何创建你的用户界面呢？为了正确的数据库设计，您还考虑哪些进一步的参数？

原文

Based on the discussion I found here: Database: To delete or not to delete records, I want to focus on reference data in particular, add a few thoughts on that, and ask for your preferred approach in general, or based on which criteria you make the decision which of the approaches available you go for.

Let's assume the following data structure for a 'request database' for customers, whereas requests may be delivered via various channels (phone, mail, fax, ..; our 'reference data table I want to mainly focus on'):

Request (ID, Text, Channel_ID)
Channel(ID, Description)

Let's, for the beginning, assume the following data within those two tables:

Request:

ID    | Text                                         | Channel_ID
===============================================================  
1     | How much is product A currently?             | 1 
2     | What about my inquiry from 2011/02/13?       | 1
3     | Did you receive my payment from 2011/03/04?  | 2

Channel:

ID    | Description
===============================================================  
1     | Phone
2     | Mail
3     | Fax

So, how do you attack this assuming the following requirements:

Channels may change over time. That means: Their descriptions may change. New ones may be added, only valid starting from some particular data. Channels may be invalidated (by some particular date)
For reporting and monitoring purposes, it needs to be possibly to identify using which channel a request was originally filed.
For new requests, only the currently 'valid' channels should be allowed, whereas for pre-existing ones, also the channels that were valid at that particular date should be allowed.

In my understanding, that clearly asks for a richer invalidation approach that goes beyond a deletion flag, probably something incorporating a 'ValidFrom / ValidTo' approach for the reference data table.

On the other hand, this involves several difficulties during data capture of requests, because for new requests, you only display they currently available channels, whereas for maintenance of pre-existing ones, all channels available as of the creation of this record need to be displayed. This might not only be complicated from a development point of view, but may also be non-intuitive to the users.

How do you commonly set up your data model for reference data that might chance over time? How do you create your user interface then? Which further parameters do you take into account for proper database design?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

骄兵必败 2024-10-31 16:11:00

在这种情况下，我通常会创建另一个表，例如，channel_versions，它复制 channel 中的所有字段，并具有额外的 create_date 列（并且它有自己的 PK）当然）。对于channel，我定义了插入/更新后触发器，将新值复制到channel_versions中。现在，Request 表中的所有请求都引用 channel_versions 中的记录。对于新请求，您需要从 channel_versions 获取最新版本的通道。对于旧请求，您始终知道满足请求时频道的外观。