Postgresql – Wich solutions is better for translate some data

database-designpostgresql

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.

enter image description here

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.

enter image description here

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 ?

Best Answer

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.