|
|
|
supporting language
Thursday, May 21 2009
I'm working on my first multi-lingual web project, and its multi-lingual nature has been causing some database design headaches. Had the database been simple, without numerous relations between tables, the multi-lingual requirements could have been satisfied with a language column in those tables having content in the various languages (this was the initial language support designed by my colleague in California). But I found I was dealing with a situation where users could be answering questions in either English or Spanish that nevertheless had to be keyed to the same "question." I could have had those question entities be "languageless" and get their text from some sort of language table, but this system would have been at unnecessary variance with the other tables that needed to be bilingual. So I decided to implement a "low level" multilingual solution, one that could support an arbitrary number of languages and provide those translations for any field in an otherwise non-multilingual database while providing a completely regularized editing system for all multilingual attributes. Since my particular database management system is increasingly coming to resemble its own self-contained computational system (not quite an operating system, but we could use that term quasi-metaphorically, as others have done regarding, say, Firefox, Google, or even Facebook), I could insert this language support at such a low level that translations would automatically be available to all tables in a database. The low-level code would look to see how many languages were in the "language" table and then provide parallel textareas for text columns in the table editors. The actual translated contents of those columns wouldn't end up in those tables; they'd end up in a "translation" table keyed to the table name, column name, primary key value, and language. But this would happen in a manner entirely transparent to the administrator using my database management system. On the other side (the implementation side) I would provide a language-aware function that would take a SQL string and a language ID and spit out a properly-translated recordset. It was an elegant system, although the amount of database traffic it generated was astonishing.
For linking purposes this article's URL is:
http://asecular.com/blog.php?090521 feedback
previous | next |
