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:
CITY_NAME_ENGwith english translation, and keep the value to NULL for record without translation.
CITY_NAMEand add a new column called
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?
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.
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.