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?

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

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.

  • This is exactly how I would do it. – Eric King Sep 13 '19 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 Sep 16 '19 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 Sep 16 '19 at 13:47

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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