数据库设计：如何存储翻译后的数字？
这是一个一般的数据库设计问题。假设如下表：（
======================================================================
| product_translation_id | language_id | product_id | name | price |
======================================================================
| 1 | 1 | 1 | foobar | 29.99 |
----------------------------------------------------------------------
| 2 | 2 | 1 | !@#$%^ | &*()_ |
----------------------------------------------------------------------
假设 language_id = 2 是某种不基于拉丁字符等的语言）
我将翻译后的价格存储在数据库中是否正确？虽然它允许我正确显示翻译，但我担心当我想对它们进行数学运算时它会给我带来问题（例如，向 &*()_ 添加 10% 的销售税）。
处理数字翻译的好方法是什么？
This is a general DB design question. Assume the following table:
======================================================================
| product_translation_id | language_id | product_id | name | price |
======================================================================
| 1 | 1 | 1 | foobar | 29.99 |
----------------------------------------------------------------------
| 2 | 2 | 1 | !@#$%^ | &*()_ |
----------------------------------------------------------------------
(Assume that language_id = 2 is some language that is not based on Latin characters, etc.)
Is it right for me to store the translated price in the DB? While it allows me to display translations properly, I am concerned it will give me problems when I want to do mathematical operations on them (e.g. add a 10% sales tax to &*()_).
What's a good approach to handling numerical translations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您可以以编程方式将“29.99”转换为“&*()_”，那么我会将价格放入产品表中，并将其翻译保留在显示层中。如果您将其存储两次，那么您将遇到两个明显的问题：
当您需要更新价格时，第一个问题会让您非常头疼，并且您的会计师会讨厌您把账簿搞得一团糟。
每当您需要在数据库内进行任何计算或比较时，第二个问题都会使您的数据库讨厌您。一遍又一遍地调用
CONVERT(string AS DECIMAL)会产生成本。您可以将价格以数字形式保存在产品表中（用于计算、排序等），然后将本地化翻译作为字符串保存在翻译表中。但这种方法只会放大上述两个问题。但是，如果您需要人工翻译您的数字，那么这种方法可能是必要的。如果您遇到这个问题，那么您可以通过在每次更新后运行某种健全性检查器来减轻一致性问题，您甚至可以将健全性检查器包装在某种触发器中。
If you can programatically convert "29.99" to "&*()_" then I'd put the price in the product table and leave the translation of it the display layer. If you store it twice then you will have two obvious problems:
The first issue will cause you a lot of head aches when you need to update your prices and your accountants will hate you for making a mess of the books.
The second issue will make your database hate you whenever you need to do any computations or comparisons inside the database. Calling
CONVERT(string AS DECIMAL)over and over again will have a cost.You could keep the price in numeric form in the product table (for computation, sorting, etc.) and then have the localized translation in the your translation table as a string. This approach just magnifies the two issues above though. However, if you need to have humans translating your numbers then this approach might be necessary. If you're stuck with this then you can mitigate your consistency problems by running a sanity checker of some sort after each update, you might even be able to wrap the sanity checker in a trigger of some sort.