This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another:
Suggestion for database structure for multilanguage (2011 Jun)
What's the best database structure to keep multilingual data? (2010 Feb)
What are best practices for multi-language database design? (2009 May)
Schema for a multilanguage database (2008 Nov)
The most popular database scheme for backing multilingual user interfaces seems to be having all the translated texts of all languages in one table with 3 columns: the text id, the language code, and the text itself. The text id and the language code together make up the primary key.
That's all very fine, but now consider a complication: suppose that the texts need to be searchable. Suppose, for example, that this is a multi-language e-shop. This means that for every product category entered into the database, the shop owner will enter the name of the product category in each and every one of the N supported languages, and then the shopper will be able to search for the product category by name, in their own language.
There is a problem: Collation.
Different languages have different collation sequences, and the collation sequence which works for one language does not work for another. So if all texts of all languages are on a single column, what collation sequence are they going to have? How are we going to query the database to find the text id of a specific text? While in a web product search accuracy and performance might not be awfully important, for the purposes of this discussion let us assume that they really matter.
Most database administrators are familiar with the concept of collation in the sense of "the collation of the database". Luckily, that's just the default collation, which is used if no other collation information is present, but there exist other places too, where collation can be specified:
The SQL CREATE INDEX command supports a collation specification. (Though rumors have it that Microsoft SQL Server does not support it; does anyone know about that?)
The SQL SELECT statement also supports collation, but in this case the collation specification works as a function, causing an index scan instead of an index lookup, something which might be impermissible if we want performance. (Then again, if that's the best we can have, it might be better than nothing.)
I also hear that on Microsoft SQL Server you can have non-persisted, computed columns on which you can specify collation and create a filtered index, though I have never heard of this before, and if it is a Microsoft-SQL-Server-only feature, then I'd rather refrain from using it, no matter how cool and well thought out it is.
So, in light of all that, how do we structure our database, and how do we perform our queries, if the goal is an updatable and searchable multilingual database?
This question was inspired by a discussion that took place here: how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?