Create a column mostly with null data SQL like

by Accollativo   Last Updated September 13, 2019 09:05 AM

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 row 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?

Answers 2

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.

Bohdan Stupak
Bohdan Stupak
September 13, 2019 10:22 AM

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.

Jon Raynor
Jon Raynor
September 13, 2019 18:45 PM

Related Questions

Updated September 04, 2019 07:05 AM

Updated April 10, 2018 12:05 PM

Updated October 26, 2018 10:05 AM

Updated October 09, 2018 13:05 PM

Updated July 17, 2019 14:05 PM