MySQL Multi-lingual Database Design (multi-table vs. multi-column)

Question

Currently, the database has 2 separate languages: English & French. Knowing the fact that I may add more languages in the future, which one of these three solutions would be more appropriate?

#1. Creating separate page_translation tables for each language:

   CREATE TABLE page_translation_en (
     id INT,
     page_id INT,
     translation LONGTEXT
   );

   CREATE TABLE page_translation_fr (
     id INT,
     page_id INT,
     translation LONGTEXT
   );
  
   ...

#2. Adding translation columns to the pages table:

   CREATE TABLE pages (
     id INT,
     page_id INT,
     translation_en LONGTEXT,
     translation_fr LONGTEXT,
     ...

   );

#3. Creating a page_translations table that contains all translations

CREATE TABLE page_translations (
  id INT,
  page_id INT,
  language_id INT,
  translation LONGTEXT
);

Would +1M pages and 3 separate languages influence the query performance for solutions #2 & #3 where all the translations are in one table? also, considering that I would like to search the translation contents...

Jared Beck Jared Beck · Accepted Answer · 2020-11-25T00:10:29

Would +1M pages and 3 separate languages influence the query performance for solutions #2 & #3 where all the translations are in one table?

Assuming typical page lengths, eg. a news article, 1M pages is not a huge number. On a system with reasonable resources and correct indexes, the join between the pages and page_translations tables should be fast. The difference in performance between solutions #2 and #3 should be negligible to the end-user.

also, considering that I would like to search the translation contents...

In this case, solution #3 is easier to query. Solution #2 would result in an awkward where clause, eg. where translation_en like .. or where translation_fr like .. etc.

PS: I'd recommend researching the design of open-source CMS projects like WordPress. I can't speak from experience, but I'm sure this is a design problem they've given a lot of thought to.

MySQL Multi-lingual Database Design (multi-table vs. multi-column)

1 Answers