0

My requirement is as follows: An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table:

1. One column for each language: So, my table columns will be as below, which means it will have one column for each language. So, in future if I add one more language, lets say German then I will add one more column LABEL_GM and will write update queries to add German labels.

    "LABEL_EN" VARCHAR2(1000 BYTE), 
    "LABEL_FR" VARCHAR2(1000 BYTE), 

2. One row for each language: So, my table columns will be as below, which means it will have one row for each language. So, in future if I add one more language, lets say German then I will write insert queries to add German labels.

    "LABEL" VARCHAR2(1000 BYTE), 
    "LANGUAGE" VARCHAR2(2 BYTE), 

Questions:

  1. Which is more space and performance efficient way in today situation when I am having only 2 language support.
  2. Which is more space and performance efficient way from future perspective, in case if we need to add one more language support, so main concern is how efficient will be data insertion/updation.
3

This question is very similar with your other question.

As already stated there, I would prefer a translation table like this:

Table Translations

Code (PK) (Your translation code)
type (mobile, desktop, maybe something else later)
language_id (your language code - 1:EN, 2:FR, 3:DE,...)
value (your translation)
2

Further to Ionic's answer, two good reasons for keeping languages as separate rows rather than separate columns are that:

  1. Your queries for retrieving localized strings are cleaner because the language selection is via a parameter to a where clause rather than using a dynamic SQL or complex branching conditions.

  2. Adding a new language to your localization scheme does not require any changes to either your localization source code or your table structure.

Please beware: Using dynamic SQL is precisely when you need to be concerned about SQL injection. It all depends on exactly how you do it. You need to make sure anything that comes from the user, including things spoofed by malicious browser emulation are escaped to avoid SQL injection. Parameterized SQL queries are much less prone than dynamic SQL to this type of attack.

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.