存储具有唯一值的时间段
我遇到一个问题，需要根据“期间”的销售目标跟踪实际销售额。期间可以是一天、一个月、一个季度或一年。
最初，我想出了一个PeriodType 的枚举（分别为1-4、日、月、季度、年）。鉴于此，我需要 3 个值来唯一地描述一个周期：1) 周期类型，2) 周期年份的整数（例如 2010、2011 等），3) 周期编号的整数。
例如，{1,2011,38} 是 2011 年 2 月 2 日。或者，{3,2,2010} 是 2010 年第二季度。或者 {2,4,2012} 是月份2012 年 4 月。
拥有一个公开这三个属性的对象不是问题。但就数据库而言，我试图弄清楚如何存储实体（例如目标或销售）与期间的关系 - 在每个需要一个表的表中拥有 3 列似乎不太优雅。时期关系。一种方法是为周期建立一个表，其中包含一个主键和其他 3 个值 - 但像这样持久化周期本身是没有意义的，因为它是一个可以计算的时间概念。
用单个唯一值表示我对“期间”的定义的最佳方式是什么？或者我对周期的定义被误导了，有一种更简单的方法可以做同样的事情吗？
I have a problem in which I need to track actual sales against a sales goal for a "Period". A Period can be a day, month, quarter or year.
Initially, I have come up with an enum for PeriodType (1-4, Day, Month, Quarter, Year respectively). Given this, I would need 3 values to uniquely describe a Period: 1) a PeriodType, 2) an integer for a PeriodYear (eg. 2010, 2011, etc.) and 3) an integer for a PeriodNumber.
So, for instance, {1,2011,38} would be the day February 2, 2011. Or, {3,2,2010} would be the second quarter of 2010. Or {2,4,2012} would be the month of April, 2012.
Having an object that exposes these three properties is not a problem. But in terms of the database, I am trying to figure out how to store the relationship of an entity (like a Goal or a Sale) to a Period - it doesn't seem very elegant to have 3 columns in every table that requires a Period relationship. One way would be to have a table for Period that has a primary key and the other 3 values - but it doesn't make sense to persist the Period itself like that because it is a time concept that can be calculated.
What would be the best way to represent my definition of a "Period" with a single unique value? Or is my definition of a Period misguided and there is a simpler way to do the same thing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最简单的解决方案是根据最低粒度（即一天）存储销售额。然后，将这些销售额汇总起来以实现长期跟踪销售额的目标就变得很简单了。
如果这不能满足您的要求，您应该更详细地解释如何收集和使用数据，特别关注为什么您需要更复杂的数据结构。
时区问题是个问题，但不是问题。重要的是就时间的单一事实点达成一致，该事实点应该是数据库，并以 UTC 格式保存所有日期时间。客户端在检索数据时必须将 UTC 转换为时区，反之亦然。
这在实践中有多容易在很大程度上取决于您的数据库风格，但几乎每个 RDBMS 都应该支持此功能。
The simplest solution would be to store Sales against the lowest level of granularity, i.e. a day. In would then be a simple matter to aggregate those sales for goals which track sales at a longer period.
If this does not meet your requirements you should explain in a bit more detail how the data is gathered and used, with a specific focus on why you need a more complicated data structure.
The question of timezones is problematic but not a showstopper. The important thing is to agree on a single point of truth for time, which ought to be the database, and hold all date times in UTC. Clients would have to translate UTC into their timezone when retrieving data and vice versa when saving it.
How easy this is in practice depends very much on your flavour of database, but pretty much every RDBMS ought to support this functionality.