Wich solutions is better for translate some data?

Question

I've a little mobile app who need some translation. Currently, I just want to translate my app in one language, I don't think I'll need more. So I'm facing a problem, should I translate stuff in database, or in translations files on client side ?

The user will be able to select his language, but first i need to determine it with the locale variable for the registration (because he must select his country at registration).

First I tought about creating one translation table for each table who need it.

But it seems to not be the good solution if I need to add more language, I'll have to alter all translations tables.

So, my second solution is to create a language table, keep the translation table for each table but as one-to-many relationship.

I think this solution is better, but I'm not sure about performance. I know it's a little app and the question does not really arise, but we never know.

Now, I'm stuck because It means that every time I need translation, I have to query the database. There are tons of contries, and I'll like to have auto completion. Fruits will be displayed in several pages and used in others tables.

So I thought about keep translations on client side within json files. For exemple translations/fr_FR.json

"fruits": [
  {
    "id": 1,
    "original": "banana",
    "translation": "banane"
  },
  {
    "id": 2,
    "original": "apple",
    "translation": "pomme"
  }
],
  "countries": [
  {
    "id": "ES",
    "original": "spain",
    "translation": "espagne"
  }
]

Everytime I need translation, I can use a function to get the translation from the file.

It means more calculations, and user will have to update his app if translation has changed.

Wich solution is better ?

Answer 1

I think your second solution is better. Never worry about small joins between several tables: when properly indexed, PostgreSQL will use an efficient nested loop join. Databases are designed for a normalized table layout like this, it is the best solution for an OLTP application.

Answer 2

You should only store your data in one language and handle the translating of text on the application side, for a multitude of reasons:

1. Different languages use different characters which may affect the data type and collation of how you store the translated data. This can affect you from a flexibility standpoint and potentially a performance standpoint (depending on the size of your data) and what collations you're using elsewhere.

2. Different languages have different dialects which means there's more rules to how you store the translated words. For example, Spanish spoken in parts of Spain may vary from how it's spoken in Brazil or Mexico.

3. Per #2, there may be different words in each of those dialects for the corresponding English word, and you'll then need to store more translations than anticipated.

4. Even within the same dialect of a language, a word may translate differently depending on the context it's used it. Building out a relational model to manage all the contexts of a given word sounds like a nightmare to me.

5. There's already a multitude of APIs out there that can be consumed on the application side and take care of all the aforementioned issues, such as Google's Translate API. There are other free ones as well. This is probably the simplest solution to this kind of problem.