查找表中记录的历史记录
我希望“查找表”一词选得好，我的意思是例如具有以下费率的费率表（查找）：
便宜：15 美元，-
中等：30 美元，-
昂贵：45 美元，-
我们处于这种情况对于给定的实体（我们称之为“故障”，它是设备、空调、电梯、起重机、厕所等的故障），聘请施工人员来修复该设备。 该建筑商具有以下三种（虚构）费率：廉价、中等和昂贵。
当施工人员修复故障时，他从技术上输入工作时间和费率（当高级人员完成工作时，“昂贵”，当初级人员完成工作时，“便宜”）
，然后我们从故障表到费率表。
因此，当需要打印发票时，我们可以通过 FK 获取费率并从故障记录中获取工作时间。
问题是，当建造商更改其费率，并且您在几个月后重新计算旧发票时，会计算发票的其他金额，因为记录已更改。
所以我们必须构建某种历史，这就是问题：如何做到这一点？
我想到了两种不同的情况，问题是：其中一种是好的吗？有更好的方法吗？
1 在费率表中添加有效起始日期和有效截止日期字段，因此当您编辑值时，您实际上会创建一条包含新有效日期的新记录。缺点是您必须始终牢记特定日期的费率，这对于当前情况（此时的实际费率）是没有必要的。
2 不要将 FK 从故障转移到速率，但是当您在故障处设置速率时，只需将 VALUE 从速率复制到故障即可。缺点是，当故障仍可编辑时，当您编辑速率时，故障的速率不会更新。而且，当您编辑故障时，您会看到一个下拉框，其中有 3 个值可供选择，其中没有一个与当前值相同。
至此，感谢您阅读整篇文章！
i hope the term 'lookup table' is well chosen, what i mean is for example a rate table (lookup) with the following rates:
cheap: $15,-
Medium: $30,-
expensive: $45,-
we're at the situation that for a given entity (we call it 'fault', it is a malfunction of a device, airco, elevator, krane, toilet etc.) a constructor is hired to fix that device.
that constructor has these three (made up) rates: cheap, medium and expensive.
When the constructor fixes the fault, he enters the hours worked and the rate (when a senior has done the job, 'expensive', and when a junior has done the job, 'cheap')
technically, we then add a FK from the Fault table to the Rates table.
So when the invoice has to be printed, we get the rate via the FK and the hours worked from the fault record.
Problem is that when the constructor changes his rates, and you recalculate an old invoice months later, other amounts are calculated for the invoice because the record has changed.
So we have to construct some kind of history, and that's the question: how is that done?
what i've come up with is 2 different situations, and the question is: is one of these a good one are there better ways?
1 add a valid-from and valid-until field at the rate table, so when you edit a value, you in fact create a new record with new valid dates. downside is you have to always get the rates with a specific date in mind, which for the current situation (the actual rate at this moment) is not neccessary.
2 don't put a FK from fault to rate, but when you set a rate at a fault, you just copy the VALUE from rate to fault. downside is that when the fault is still editable, when you edit the rate, the fault's rate is not updated. And, when you edit a fault, you get a dropdown box with 3 values to choose from, non of which are the same of the current values.
At this point thanks already for reading this entire post!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不喜欢#2；如果可以的话，我从不喜欢用实际值替换关系（非规范化）。此外，这也使得审计变得更加困难；如果汇率中有一个奇怪的值，它是从哪里来的？
不过，#1 的问题是，如果由于某种原因您更改了发票的日期，它可能仍应具有与最初创建时相同的费率。
出于这些原因，我建议执行#1 的部分，其中费率更改始终创建一个新行，但然后将每个故障链接到实际应用的费率（即，而不是依赖于加入费率的日期） ，实际上存储带有故障的速率 ID）。
查找当前汇率的一种方法是查找没有结束日期的汇率。或者，根本不使用结束日期（下一个费率的开始日期被视为上一个费率的结束日期），而仅按日期排序并取最后一个。
I don't like #2; I never like replacing relationships with actual values (denormalizing) if I can help it. Also, it makes auditing a lot harder; if there's a weird value in for the rate, where did it come from?
The problem with #1, though, is that if for some reason you change the date of the invoice, it should probably still have the same rate that it had when it was originally created.
For these reasons, I'd recommend doing the part of #1 where a rate change always created a new row, but then link from each fault to the rate that was actually applied (i.e. rather than relying on the date to join to a rate, actually store a rate id with the fault).
One approach to finding the current rate is just to look for the one that has no end date. Or alternately, don't use end dates at all (the start date of the next rate is treated as the end date of the previous rate), and just sort by date and take the last one.
Programmers.SE 上对此进行了很好的讨论
如何商店价格有生效日期
这是一个众所周知的问题，使用生效日期是解决这个问题的最佳方法。
There was a good discussion of this over on Programmers.SE
How to Store Prices That Have Effective Dates
It's a well-known problem and using effective dates is the best way to do it.
我建议保留一份承包商费率表，按日期排序。当承包商的费率发生变化时，不更改现有费率而是添加新条目。当您需要获取当前费率时，按时间戳降序排序并限制 1. 添加当前费率的日期条目输入每个作业记录，然后您可以执行简单的联接以立即获取所有信息。
I'd suggest keeping a table of contractor rates, ordered by date. When a contractor's rates change, instead of changing the existing rate add a new entry. When you need to get the current rate, sort by the timestamp descending and limit 1. Add the date entry for the current rate entry to each job record and then you can perform a simple join to get all the information at once.