Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 2 '12 at 7:32

This question came from our site for professional and enthusiast programmers.

    
I'm not sure, but I think this is a question best posted on Programmers. –  Biosci3c Dec 30 '11 at 20:51
2  
If a Microsoft product-only feature is really cool and well thought out, it should have fair chances of gaining support in similar products by other vendors in time. Just a thought. –  Andriy M Dec 31 '11 at 14:55
    
A respectable thought. –  Mike Nakis Dec 31 '11 at 14:56

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
Thank you very much for your extensive reply. A couple of questions: 1. How 'standard' are all of the above? In other words, are they applicable to all RDBMSes that adhere to SQL2003 or SQL2008 standards, or are they pertinent to, say, Microsoft SQL Server only? 2. What do you think is more of a pain: adding a new column for each language, or adding a new table? (I would think that adding a new table would be more of a pain, but I may be wrong.) –  Mike Nakis Jan 2 '12 at 11:22
    
Not standard at all :) SQL_VARIANT and full text indexing are proprietary (though ORACLE does have the ANYDATA type; not sure if it has any sort of similar text searching component). Which of the two options is more of a pain is really tough to say in general: it depends on the rest of the implementation architecture (the indirection I was talking about). –  Paul White Jan 2 '12 at 11:43
    
I probably use a separate table rather than a separate columns for better physical layout: it was my answer saying that that inspired this question dba.stackexchange.com/a/9954/630 –  gbn Jan 2 '12 at 12:58
    
@gbn Read that and up-voted :) Odd that there's no really perfect solution to this problem though. –  Paul White Jan 3 '12 at 3:27

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.

share|improve this answer
    
The problem with this is that it is very un-relational. –  Mike Nakis Dec 30 '11 at 19:52
    
Yes, my experience is, that text search, whether db supported or self done, is hard to integrate relationally. Thanks for giving a point anyway. –  Joop Eggen Dec 30 '11 at 20:14
1  
On a question of this kind, every valid opinion deserves an upvote. –  Mike Nakis Dec 30 '11 at 22:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.