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.