1

In postgresql I have a table with a list of cities. One row for city. Around 15000 rows. A column is CITY_NAME, now I have to add an english translation of the city name. The problem is that only 10 cities out of 15000 have an english translation. I was thinking about two solutions:

  1. Add a new column CITY_NAME_ENG with english translation, and keep the value to NULL for record without translation.
  2. Duplicate the rows of the cities that have an english translation, keep only a column CITY_NAME and add a new column called LANGUAGE.

The second solution seems more elegant (and would be good if other languages will be add), but the select of all cities in a particular language seems tricky.

Are there other solutions? Which is better? NULL value occupy memory?

Improve this question
11
  • How likely is it that you will add another language later, and then another?
    – Robert Harvey
    Commented Sep 13, 2019 at 9:18
  • Good question. I suppose we will not add another language (surely not for the next 3 years).
    – Accollativo
    Commented Sep 13, 2019 at 9:44
  • "Add a new row CITY_NAME_ENG" - are you sure you're not confusing rows and columns?
    – Bohdan Stupak
    Commented Sep 13, 2019 at 9:58
  • @BohdanStupak you've right, I edited the post.
    – Accollativo
    Commented Sep 13, 2019 at 9:59
  • Am I correct that in the second approach you want to add the table with city name English localization?
    – Bohdan Stupak
    Commented Sep 13, 2019 at 10:04

2 Answers 2

Reset to default
8

A table called CityTranslations could be of help or more generically Translations. Make a FK to the table that has the city column.

The table could have a column for the translation value and the language code. Only have records for values that exist so you don't have a column with lots of NULL values.

Sounds like you would start with 10 rows and as more cities have translation value more rows could be added.

The translation table would have a a 1 to many relationship with parent table since each parent record could have multiple translations depending on how many languages are supported. A table with no translations would return no data.

Improve this answer
3
  • This is exactly how I would do it.
    – Eric King
    Commented Sep 13, 2019 at 23:54
  • This should be the correct solution but making a query to select all cities that have an English name otherwise if they have not, the Italian name, become slow and tough. Despite this should be the right solution I'm not sure it is in this case.
    – Accollativo
    Commented Sep 16, 2019 at 8:00
  • @Accollativo - I agree this isn't the best overall solution. Usually with data, translations are not necessary. For example "123 Main Street" would be "123 Main Street", regardless of language. On the UI, if I had a label called "Address", that's what would be translated, usually in the form of some sort of resources file(s) on the client.
    – Jon Raynor
    Commented Sep 16, 2019 at 13:47
1

Taking into account that you're not planning to have a new translation for the next couple of years I prefer the first approach because it eliminates the need for inner join when obtaining English translation. Which in turn would be beneficial for performance.

In any case, I suggest you consider if localization could be made somewhere else. I.e. on the front-end side of your application. In case you're doing js on your front-end every mature js framework has localization mechanism. That would save you some precious calls to the back-end.

Improve this answer
1
  • 2
    Database servers are really good at doing joins, i don't think avoiding them will lead to any worthwhile performance gains.
    – Andy
    Commented Sep 14, 2019 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.