Efficient Database schema for multi-lingual content?

Question

Assume I have a table for blog posts, posts, and schema as

id
author
title
content

and each post can belonged to some categories

id
name
descrtiption

So a blog post can belong to multiple categories.

Obviously title and content in posts, and name and description in categories can be translated to different languages.

So, given the existing db schema design, what would be the most efficient and reasonable way to extend the support for a multi-lingual application?

Answer 1

The least work would be to put a language code in each table. The problem then is there is no way to show that, say, post 612 (English) is a translation of post 943 (Spanish).

To achieve that level of cross-reference it will be necessary to separate the post's existence from its content. There will be these tables:

Language
 Language_id
 Name
Post
 Post_id
Category
 Category_id
PostCategory
 Post_id
 Category_id
CategoryLanguage
 Category_id
 Language_id
 Name
 Description
 ...
PostLanguage
 Post_id
 Language_id
 Title
 Content
 ...

Ideally we would want both a post and its categories translated at the same time. It is difficult to enforce the existance of both simultaneously using declarative constraints. If this is a requirement it would be simpler to push enforcement into the application.

When displaying a post the corresponding categories should be those in the same language. To this end each user could be allowed to choose a language. There will need to be rules to determine what happens if an item has not been translated to the user's preferred language. Maybe a system default language is defined?

Answer 2

I have used the following style of schema for multi-lingual support:

 +---------------+
 | CATEGORIES_B |
 +---------------+
 | category_id |
 | category_code |
 | ... |
 +---------------+
 |
 |
+-------------------------+
| CATEGORIES_T | +---------------+
+-------------------------+ | LANGUAGES |
| category_id | +---------------+
| translation_language_id |----| language_id |
| category_name | | language_code |
| category_description | +---------------+
| ... | | |
+-------------------------+ | |
 | | |
 | | |
+-------------------------+ | |
| CATEGORIES_L | | |
+-------------------------+ | |
| category_id | | |
| language_id |----------+ |
| translation_language_id |--------------+
+-------------------------+

LANGUAGES contains all the languages that you wish to support in your application, it has a primary key of language_id:

1 ENG
2 FRA
3 ESP

CATEGORIES_B contains the non-translatable attributes of each category, it has a primary key of category_id:

1 DBDES

A record would be inserted into CATEGORIES_T for each available translation, it has a compound primary key of category_id and translation_language_id:

1(DBDES) 1(ENG) Database Design The logical and/or physical design of a database

A record would be inserted into CATEGORIES_L for each supported language; indicating this was the translation to use; it has a compound primary key of category_id and language_id:

1(DBDES) 1(ENG) 1(ENG)
1(DBDES) 2(FRA) 1(ENG)
1(DBDES) 3(ESP) 1(ENG)

CATEGORIES_L should contain every value in the Cartesian Product of CATEGORIES_B and LANGUAGES.

As the translations became available the records in the CATEGORIES_T and CATEGORIES_L tables would be amended as appropriate:

1(DBDES) 1(ENG) Database Design The logical and/or physical design of a database
1(DBDES) 2(FRA) Database Design La conception logique et/ou physique d'une base de données
1(DBDES) 1(ENG) 1(ENG)
1(DBDES) 2(FRA) 2(FRA)
1(DBDES) 3(ESP) 1(ENG)

And eventually:

1(DBDES) 1(ENG) Database Design The logical and/or physical design of a database
1(DBDES) 2(FRA) Database Design La conception logique et/ou physique d'une base de données
1(DBDES) 3(ESP) Diseño de base de datos El diseño lógico y/o física de una base de datos
1(DBDES) 1(ENG) 1(ENG)
1(DBDES) 2(FRA) 2(FRA)
1(DBDES) 3(ESP) 3(ESP)

I have used this method when I did not have every translation available at the time the category information was to be inserted. It also indicated, when multiple but not all translations were available, which translation should be used for each language.

• Marcus Vinicius Pompeu: A very noteworthy detail: you introduced _code for both the Languages and Categories_B tables. It's very important to have a human-readable Id for primary entities, and such care denotes a seasoned architect.