Database Design: how to store translated numbers?

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 (eg add a 10% sales tax to &*()_ ).

What's a good approach to handling numerical translations?

---

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:

You will end up with consistency problems because you're storing the same thing in two different places in two different formats.

You will be storing numeric data in text format.

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.

链接地址: http://www.djcxy.com/p/37462.html

上一篇: 多语言数据库设计：耦合翻译表方法

下一篇: 数据库设计：如何存储转换后的数字？