将历史记录存储在数据库中
关于在数据库中存储历史记录，使用 DateEnd（例 1）还是 Duration（例 2）更好？
或者请随意建议另一种最有效的方法。
如果其中一个示例被证明是正确的方法，我是否还应该对这些示例之一进行其他更改？使用的数据库是 MySQL，尽管我认为它与这里的方法没有关系。
In regards to storing history within a database, is it better to use a DateEnd (Ex. 1) or a Duration (Ex. 2)?
Or please feel free to even suggest another approach that would be the most effective.
Are there other changes that I should make to one of these Examples if one proves to be the correct approach? DB being used is MySQL although I don't think it has a bearing on the approach here.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
对于这个问题有两个观点——第一，业务领域是什么？在您的示例中，您使用了“订阅” - 这些通常以“每月”、“每周”等形式出售。在所有其他条件相同的情况下，我更希望我的数据库尽可能与业务概念保持一致。您甚至可以创建一个“subscription_type”表，并从该类型中导出描述的持续时间。
这通常与数据库的执行需求发生冲突。从这个角度来看，我会弄清楚最常见的查询是什么，并看看是否可以使数据库设计以尽可能少的类型转换或计算进行工作。例如，如果您可以要求 dateEnd < ，则查找订阅在给定日期到期的所有记录会容易得多（而且可能更快）。 targetDate，而不是通过将持续时间添加到开始日期来计算日期。
There are two perspectives on this one - firstly, what's the business domain? In your example, you've used "subscription" - these are often sold as "monthly", "weekly", etc. All other things being equal, I prefer my database to align to business concepts when possible. You might even go so far as to create a "subscription_type" table, and derive the duration of the description from the type.
That often clashes with the need for your database to perform. From that point of view, I'd work out what the most common queries are going to be, and see if you can make your database design work with the minimal amount of type conversion or calculation possible. Finding all records where the subscription expires on a given date, for instance, is a lot easier (and probably faster) if you can ask for dateEnd < targetDate, rather than calculating the date by adding the duration to the start date.
如果您有开始日期和结束日期，您始终可以（或应该始终能够）计算持续时间。如果您有开始日期和持续时间，您始终可以（或应该始终能够）计算结束日期。
您还可以记录所有三个并强制执行行约束，以确保它们不能“不匹配”。
然而，“结束日期”数据的一种非常常见的用法是过滤掉“不是当前”的行：类似于 WHERE END_DATE >当前系统日期()。如果您有这种用法，那么“省略”结束日期可能是不可取的。
If you have start date and end date, you can always (or should always be able to) compute duration. If you have start date and duration, you can always (or should always be able to) compute end date.
You can also record all three and enforce a row constraint to the effect that they cannot "mismatch".
However, one very frequent kind of usage of the "end date" datum, is to filter out rows that are "not current" : something like WHERE END_DATE > CURRENTSYSTEMDATE(). If you have that kind of usage, then it is probably not advisable to "leave out" the end date.
我会存储结束日期而不是持续时间。您可以根据需要计算持续时间。似乎存储测量点而不是测量值更有意义。
I would store the end date as opposed to the duration. You can calculate the duration when needed. Seems to make more sense storing measurement points instead of measurements.
持续时间等派生值不需要存储在数据库中。
捕获行历史记录的方法在某些情况下会起作用，但要真正捕获所有行的所有更改，您可能需要另一个表，例如 subscription_hist ，它将由订阅插入和更新时的触发器更新。那么您只需跟踪last_modified_date 和last_person_changed_by。其他一切都可以推导出来。这样您就可以看到随着时间的推移该行发生了什么。我没有任何图片可以澄清，但如果仔细实施，这种方法将允许完全时间点恢复数据，并维护历史信息。
Derived values such as duration don't need to be stored in the database.
Your approach to capture history of a row will work in some cases, but to truly catch all changes to all rows you will likely need another table, something like subscription_hist that will be updated by a trigger on insert and update of the subscription. Then you would have to keep track only of last_modified_date and last_person_changed_by. Everything else could be derived. This way you could see what happened to the row over time. I don't have any pictures to clarify, but this method, if implemented carefully would allow for full point-in-time recovery of data, as well as maintaining historical information.