When a user browses some page and then switches a language, then the following actions should occur:
- If there’s a translation for a page he’s browsing now, then do redirect to that page.
- If there’s no translated version of that page, then do redirect to a home page.
So for example, a user browses /about-us page then he switched to German language, then he should be redirected to /uber-uns if a translation of /about-us in german exists.
Currently I have a structure like this,
CREATE TABLE `pages` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT
`language` varchar(3),
`urlSegment` varchar(250),
`content` TEXT
) DEFAULT CHARSET=UTF8;
What relationships/columns should be added to achieve that?
2
I would try to explain whomever came up with the idea of localized URL slugs that it will be pretty difficult to maintain. As the site grows bigger and gets translated to more languages, you are likely to encounter words that are similar or even the same in different languages and then you’ll have problems coming up with unique URLs, not to mention it will be difficult for the web master to find the translated page corresponding to some original. Because inevitably the webmaster will not understand all the languages.
So my recommendation is to use /en/about-us, /de/about-us, /fr/about-us, /zh_CN/about-us etc. That way you:
- don’t need any extra relations and
- you can quickly see which page corresponds to which
On the other hand if you insist on the localized URLs, all you really need to add is a column enUrlSegment (properly indexed). Or numeric ID, which is a bit more efficient, but a bit more difficult to work with as you always need to select the English URL segment for any kind of administrative interface.
Even in that case I’d still recommend having /en/about-us, /de/uber-uns etc. to avoid any potential problems with same words in multiple languages.
Of course this is really the easy part, the support for multiple languages, or internationalization (i18n). The difficult part is keeping all the translations in sync, the localization (l10n). I can recommend splitting the pages up to paragraphs using html2po from Translate Toolkit and using one of the many tools available for the po format for the translation. This way when the copywriter modifies the master version, you just run the tooling, the translators will see exactly the paragraphs that changed and you’ll run the tooling again and get translated versions exactly corresponding to the master version.