CODEWITHSUNDEEP
phpmysqldatabase-designmultilingual
Brigo
Brigo

Reputation: 1112

Multilingual website: one table or multi-tables?

I'm building a multilingual website in PHP.

At first I was considering to use JSON file with a structure like

{
  "TEXT_CODE": {
     "LANG_CODE": "TEXT",
     "LANG_CODE": "TEXT"
   }
}

But then, since the texts could be a lot, I opted for a database solution that should be more handable and readable.

Assuming we have 200 texts and 5 languages, which is the best option in terms of database design mixed with performances?

Solution 1

A single table texts_multilingual with 200 * 5 = 1000 rows to search among at each page request.

Solution 2

One table per each language texts_en, texts_it, etc. so that each research would search only among 200 rows for the text code.

I guess solution #2 is the best way in terms of performances but I would like to know the opinion of more expert developers and how they would handle it.

Upvotes: 0

Views: 298

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The first solution is the better solution. Why?

First, you have only one table and you can optimize the table for your queries, using indexes and partitioning.

Second, you can add a new language quite easily. There is no new table to add.

Third, you can readily see what languages you have in the table and whether you have translations for all texts in all languages.

For performance, you simply want an index on the table . . . texts_multilingual(language, textid, text). In fact, you might consider making this a primary key. Personally, I prefer having an auto-incremented primary key. But because this table is rarely updated, used in one specific way, and unlikely to have foreign key relationships, having a composite primary key is also reasonable.

Upvotes: 1

Related Questions