Database behind a Multilingual User Interface

Question

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?}

Answer 1

It is possible to store strings with different collations in the same column using SQL_VARIANT:

CREATE TABLE dbo.Localized
(
    text_id     INTEGER NOT NULL,
    lang_id     INTEGER NOT NULL,
    text_body   SQL_VARIANT NOT NULL,

    CONSTRAINT [PK dbo.Localized text_id, lang_id]
        PRIMARY KEY CLUSTERED (text_id, lang_id),
)
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 2057, N'Database problems' COLLATE Latin1_General_CI_AS);
GO
INSERT dbo.Localized
    (text_id, lang_id, text_body)
VALUES
    (1001, 1025, N'قاعدة بيانات المشاكل' COLLATE Arabic_CI_AS)

This design has several drawbacks (including being limited to 8000 bytes), not least in the search area: SQL_VARIANT cannot be full-text indexed, and some string comparison features (e.g. LIKE) cannot be used directly either. On the other hand, it is possible to create a regular index on SQL_VARIANT and perform the more basic comparisons (e.g. <, =, >) in a collation-aware fashion:

CREATE UNIQUE INDEX uq1 ON dbo.Localized (text_body)
GO
-- One row
SELECT
    l.*
FROM dbo.Localized AS l 
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Latin1_General_CI_AS)

-- No rows (and no collation error!)
SELECT
    l.*
FROM dbo.Localized AS l
WHERE
    l.text_body = CONVERT(SQL_VARIANT, N'Database problems' COLLATE Arabic_CI_AS)

-- One row, index seek, manual version of "LIKE 'D%'"
SELECT
    l.*
FROM dbo.Localized AS l 
WHERE
    l.text_body >= CONVERT(SQL_VARIANT, N'D' COLLATE Latin1_General_CI_AS)
    AND l.text_body < CONVERT(SQL_VARIANT, N'E' COLLATE Latin1_General_CI_AS)

We can also write the usual sort of procedures:

CREATE PROCEDURE dbo.GetLocalizedString
    @text_id    INTEGER,
    @lang_id    INTEGER,
    @text_body  SQL_VARIANT OUTPUT
AS
BEGIN
    SELECT
        @text_body = l.text_body
    FROM dbo.Localized AS l
    WHERE
        l.text_id = @text_id
        AND l.lang_id = @lang_id
END
GO
DECLARE @text SQL_VARIANT

EXECUTE dbo.GetLocalizedString
    @text_id = 1001,
    @lang_id = 1025,
    @text_body = @text OUTPUT

SELECT @text

Of course, full-text indexing is also problematic in the "single table for all translations" design, since full-text indexing (all but) requires a language id setting per column. The multiple table design described by Joop Eggen could be full-text indexed (though it would naturally require one index per table).

The other main option is to have one column per locale in the base table:

CREATE TABLE dbo.Example
(
    text_id     INTEGER NOT NULL,
    text_2057   NVARCHAR(MAX) COLLATE Latin1_General_CI_AS NULL,
    text_1025   NVARCHAR(MAX) COLLATE Arabic_CI_AS NULL,

    CONSTRAINT [PK dbo.Example text_id]
        PRIMARY KEY CLUSTERED (text_id)
)

This arrangement does have a certain simplicity to it, and works well with full-text indexing, though it does require a new column to be added with each new language, and many developers find this sort of structure inelegant and unsatisfactory to work with.

Each of the alternatives has advantages and disadvantages, and will require indirection at some level or another, so it might depend on where the developers concerned feel happiest locating that indirection. I imagine most people will prefer the multiple-table design for most purposes.

Answer 2

Evidently you want a table per language: xxx_en, xxx_fr, xxx_eo. That would be more optimal and allow language dependant collations. It would even be imaginable that you have a database per language [en][xxx], [fr][xxx], [eo][xxx].

Technical details are then of secundary importance (either one can or cannot optimize more).

The actual text keys go on a table xxx.