CODEWITHSUNDEEP
databasedatabase-designlocalizationinternationalization
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

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

What's a good approach to handling numerical translations?

Upvotes: 1

Views: 112

Answers (1)

mu is too short
mu is too short

Reputation: 434665

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:

  1. You will end up with consistency problems because you're storing the same thing in two different places in two different formats.
  2. 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.

Upvotes: 2

Related Questions