I'm working on a world-wide scheduling service which uses physical locations in different time zones. These time zones must be persisted in the database along with each location. The question is, how are they best stored?
我正在开展一项全球调度服务，该服务使用不同时区的物理位置。这些时区必须与每个位置一起保存在数据库中。问题是，它们如何最好地存储？
We currently use a custom time zone table, which maps custom integer IDs to Microsoft time zone string identifiers. I wish to store the IANA time zone identifiers instead. Our database is an SQL Server, which is accessed in C# using Entity Framework 6. We handle time using NodaTime. A solution must work well with all these technologies.
我们目前使用自定义时区表，它将自定义整数ID映射到Microsoft时区字符串标识符。我希望存储IANA时区标识符。我们的数据库是一个SQL Server，可以使用Entity Framework 6在C＃中访问。我们使用NodaTime处理时间。解决方案必须与所有这些技术一起使用。
I see two different ways to do it:
我看到两种不同的方法：
- Simply store the IANA identifier as a string along with each location.
- 只需将IANA标识符与每个位置一起存储为字符串。
- Store all IANA identifiers in a separate table and use a foreign key to link to it.
- 将所有IANA标识符存储在单独的表中，并使用外键链接到该表。
The first solution is probably the easiest, as it easily allows for new identifiers and it keeps the data closely together. It does, however, have the downside of using a lot of space.
第一种解决方案可能是最简单的，因为它可以轻松地允许新的标识符，并使数据紧密结合在一起。然而，它确实有使用大量空间的缺点。
The second solution requires us to join on the time zone table every time we need the timezone - which is rather often - but requires little space. New time zone identifiers must be added to that table if needed. It also introduces these magical integer IDs (the foreign key used), which might be mistaken as being commonly known identifiers (we currently have this problem, where IDs have moved out of the database and into an in-code dictionary used instead of the database table).
第二种解决方案要求我们在每次需要时区时加入时区表 - 这通常是 - 但需要的空间很小。如果需要，必须将新时区标识符添加到该表。它还介绍了这些神奇的整数ID（使用的外键），这可能被误认为是众所周知的标识符（我们目前有这个问题，其中ID已移出数据库并进入代码字典而不是数据库表）。
As I'm writing this, I'm wondering, if it could even be possible to create a custom time zone UDT for SQL Server, where time zones can be saved and loaded as their string identifiers, but be stored more efficiently in a user-hidden format.
正如我写的那样，我想知道，如果甚至可以为SQL Server创建自定义时区UDT，其中时区可以保存并作为其字符串标识符加载，但可以更有效地存储在用户中 - 隐藏格式。
1 个解决方案
#1
4
While either approach will work, the common practice is just to store the IANA time zone identifier as a string. They are indeed unique identifiers, so they can be treated as such. "America/Argentina/ComodRivadavia" is currently the largest string, at 32 characters - so a varchar(32) would suffice. Though, I typically use a varchar(50) just to be future-safe.
虽然这两种方法都有效，但通常的做法是将IANA时区标识符存储为字符串。它们确实是唯一标识符，因此可以将它们视为唯一标识符。 “America / Argentina / ComodRivadavia”是目前最大的字符串，长度为32个字符 - 因此varchar（32）就足够了。虽然，我通常使用varchar（50）只是为了将来安全。
The few kilobytes of storage you may save by normalizing to a lookup table usually are not worth the perf-impact of the join, IMHO. However, like any trade-off, you should evaluate both options to see which works better for your scenario. It isn't necessarily wrong to use a lookup table.
通过规范化到查找表可以节省的几千字节存储通常不值得加入的影响，恕我直言。但是，与任何权衡一样，您应该评估这两个选项，以查看哪种方案更适合您的方案。使用查找表不一定是错的。
#1
4
While either approach will work, the common practice is just to store the IANA time zone identifier as a string. They are indeed unique identifiers, so they can be treated as such. "America/Argentina/ComodRivadavia" is currently the largest string, at 32 characters - so a varchar(32) would suffice. Though, I typically use a varchar(50) just to be future-safe.
虽然这两种方法都有效，但通常的做法是将IANA时区标识符存储为字符串。它们确实是唯一标识符，因此可以将它们视为唯一标识符。 “America / Argentina / ComodRivadavia”是目前最大的字符串，长度为32个字符 - 因此varchar（32）就足够了。虽然，我通常使用varchar（50）只是为了将来安全。
The few kilobytes of storage you may save by normalizing to a lookup table usually are not worth the perf-impact of the join, IMHO. However, like any trade-off, you should evaluate both options to see which works better for your scenario. It isn't necessarily wrong to use a lookup table.
通过规范化到查找表可以节省的几千字节存储通常不值得加入的影响，恕我直言。但是，与任何权衡一样，您应该评估这两个选项，以查看哪种方案更适合您的方案。使用查找表不一定是错的。