I am developing a website that is supposed to be in at least two languages. I am a skilled developer, but I never had to deal with internationalization.
The owner of the site will create a new article in a language (say English) and link it to a category. Then, a translator will translate that article into another language (say Spanish) and it will appear in the same category in the other language. It must be the same article translated! It is not like Wikipedia, where two versions of the site coexist and sometimes you can link to the other. I intend to implement a warning "translation missing for article
blahblahblah", articles are single entities in multiple languages.
I had a few ideas so far, but I can't decide which is best:
My first idea was to have a "main language". In the
articles tables, I would have:
id SERIAL title VARCHAR content TEXT author_id FK category_id FK created_at DATETIME
I would then have a
translations table with:
lang VARCHAR(2) title VARCHAR content TEXT article_id FK
The problem is that, sometimes, the articles are created in a "secondary language" first, and then translated into the "main language" (there are some community contributions involved). This is therefore not an option.
My next approach was to have an
articles table containing pretty much nothing:
id SERIAL category_id FK created_at DATETIME
And then to have
lang VARCHAR(2) title VARCHAR content TEXT author_id FK article_id FK
Then, when I want to load the list of articles (or the content of a given one), a
JOIN gives me content from both tables, based on the current language preferences of the viewer.
This looks promising but also highly unmaintainable (although it's just my feeling).
Last idea was to have an
articles table with all the content:
id SERIAL lang varchar(2) title VARCHAR content TEXT base_article FK author_id FK category_id FK created_at DATETIME
based_on field is a foreign key to another article. If it is not null, some content from the base article will be used (category, for instance). If I want to display all articles in a given language and in a given category, the query is not trivial: I have to find all articles that match the category and the language, plus those that have the correct language, a
base_article not null, only if the
base_article has the same category.
This is not so easy to implement and so very easy to screw up. I have to think about loops, redundancy and deletions of entries are a nightmare.
I couldn't find any documentation about what is usually done. The versions up there are only presented to show what I've done so far, I'm not convinced it's good. I believe I should simply do as others do (I'm certainly not the first one to face this wall).
How does the world translate websites?