Multi-Language Text: A Table Design for Translations

Multi-language text needs one product identity and a declared rule for choosing its displayed name. Keep translations in rows keyed by product and language. Return the selected language so the fallback remains visible.

Keep Identity and Wording Separate

A translated product name does not create another product. The product table owns the stable key and base name. Translation rows hold the readable wording for specific language tags.

Use nvarchar for the text and Unicode literals prefixed with N. Language tags use a separate varchar column with an explicit comparison rule. This design selects existing translations; it does not generate translated content.

Define the Language and Blank Policies

This sample stores lowercase tags such as en-us, fr-fr, and ja-jp. Callers and loaders should normalize their accepted tags before use. The language-key collation is explicitly case insensitive, so FR-fr and fr-fr compare equal.

LanguageCode combines a byte limit with a binary LIKE pattern for ASCII letters, digits, and hyphens. A trailing blank counts as a character outside the allowed set, so the pattern rejects it. Normalize approved language tags in the application before inserting them.

NameText rejects NULL, empty strings, and strings made only of ordinary ASCII spaces. LEN ignores trailing ordinary spaces. It does not reject every Unicode whitespace character, so tabs and nonbreaking spaces remain allowed here.

Read the Translation Tables

Run the blocks below in order in a demo database, never against your application tables. The first block creates the two tables and the sample rows.

DROP TABLE IF EXISTS dbo.DemoTranslations;
DROP TABLE IF EXISTS dbo.DemoProducts;
CREATE TABLE dbo.DemoProducts(ProductId int NOT NULL PRIMARY KEY,BaseName nvarchar(200) NOT NULL);
CREATE TABLE dbo.DemoTranslations
(ProductId int NOT NULL,LanguageCode varchar(20) COLLATE Latin1_General_100_CI_AS NOT NULL,
NameText nvarchar(200) NOT NULL,
CONSTRAINT PK_DemoTranslations PRIMARY KEY(ProductId,LanguageCode),
CONSTRAINT FK_DemoTranslations FOREIGN KEY(ProductId) REFERENCES dbo.DemoProducts(ProductId),
CONSTRAINT CK_DemoName CHECK(LEN(NameText)>0),
CONSTRAINT CK_DemoLanguage CHECK(DATALENGTH(LanguageCode) BETWEEN 2 AND 20
AND LanguageCode COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^a-zA-Z0-9-]%'));
INSERT dbo.DemoProducts VALUES(1,N'Tea'),(2,N'Cup'),(3,N'Basket');
INSERT dbo.DemoTranslations VALUES(1,'en-us',N'Tea'),(1,'fr-fr',N'Thé'),(1,'ja-jp',N'茶'),(2,'en-us',N'Cup');

The sample rows contain three products. Tea has English, French, and Japanese translations, while Cup has only English. Basket has no stored translation and must retain its base name.

The primary key allows one translation per product and language. The foreign key rejects a translation for a nonexistent product. Temporary-table foreign keys are not enforced, which is why this example uses ordinary tables.

Choose Requested, Fallback, Then Base

Pass requested and fallback language tags explicitly. OUTER APPLY preserves each product when no candidate translation exists. TOP (1) and CASE rank the requested tag ahead of the fallback.

DECLARE @Requested varchar(20)='ja-jp',@Fallback varchar(20)='en-us';
 SELECT p.ProductId,COALESCE(t.NameText,p.BaseName) AS DisplayName,
 COALESCE(t.LanguageCode,'(base)') AS SelectedLanguage
 FROM dbo.DemoProducts p OUTER APPLY
 (SELECT TOP(1) x.NameText,x.LanguageCode FROM dbo.DemoTranslations x
 WHERE x.ProductId=p.ProductId AND x.LanguageCode IN(@Requested,@Fallback)
 ORDER BY CASE WHEN x.LanguageCode=@Requested THEN 0 ELSE 1 END,x.LanguageCode) t ORDER BY p.ProductId;

For this request, Tea selects the Japanese name 茶. Cup selects its English fallback, while Basket retains its base name. SelectedLanguage reports ja-jp, en-us, or the explicit base marker.

When requested and fallback are equal, the same translation is considered once. The unique product-language key removes ties for a given tag. An unavailable requested tag still allows the declared fallback and then the base name.

Test the Complete Contract

The next block compares all twelve outputs across four requested-and-fallback combinations. These include French, Japanese, unavailable German, and identical requested and fallback tags. It returns display text, selected language, and selection reason for every product.

SELECT LanguageCode,UNICODE(RIGHT(NameText,1)) AS LastCodePoint,DATALENGTH(NameText) AS NameBytes
FROM dbo.DemoTranslations WHERE ProductId=1 AND LanguageCode IN('fr-fr','ja-jp') ORDER BY LanguageCode;

DECLARE @Cases TABLE(CaseId int PRIMARY KEY,Requested varchar(20) COLLATE Latin1_General_100_CI_AS,Fallback varchar(20) COLLATE Latin1_General_100_CI_AS);
INSERT @Cases VALUES(1,'fr-fr','en-us'),(2,'ja-jp','fr-fr'),(3,'de-de','en-us'),(4,'fr-fr','fr-fr');
SELECT q.CaseId,q.Requested,q.Fallback,p.ProductId,COALESCE(t.NameText,p.BaseName) AS DisplayName,
COALESCE(t.LanguageCode,'(base)') AS SelectedLanguage,
CASE WHEN t.LanguageCode IS NULL THEN 'base' WHEN t.LanguageCode=q.Requested THEN 'requested' ELSE 'fallback' END AS SelectionReason
FROM @Cases q CROSS JOIN dbo.DemoProducts p OUTER APPLY
(SELECT TOP(1) x.NameText,x.LanguageCode FROM dbo.DemoTranslations x
 WHERE x.ProductId=p.ProductId AND x.LanguageCode IN(q.Requested,q.Fallback)
 ORDER BY CASE WHEN x.LanguageCode=q.Requested THEN 0 ELSE 1 END,x.LanguageCode) t
ORDER BY q.CaseId,p.ProductId;

Seven separate writes test the foreign key, duplicate language alias, NULL values, and invalid underscore tag. Empty and spaces-only names supply the remaining cases. Each write sits in its own TRY block, so the error number is recorded and nothing is stored.

DECLARE @Errors TABLE(TestCase nvarchar(40),ErrorNumber int);
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(999,'fr-fr',N'Orphan');
  INSERT @Errors VALUES(N'Missing product',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'Missing product',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(1,'FR-fr',N'Duplicate');
  INSERT @Errors VALUES(N'Duplicate language alias',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'Duplicate language alias',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(2,'fr-fr',NULL);
  INSERT @Errors VALUES(N'NULL name',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'NULL name',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(2,'fr-fr',N'');
  INSERT @Errors VALUES(N'Empty name',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'Empty name',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(2,'fr-fr',N'   ');
  INSERT @Errors VALUES(N'ASCII spaces only',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'ASCII spaces only',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(2,NULL,N'Cup');
  INSERT @Errors VALUES(N'NULL language',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'NULL language',ERROR_NUMBER()); END CATCH;
BEGIN TRY
  INSERT dbo.DemoTranslations VALUES(2,'fr_fr',N'Tasse');
  INSERT @Errors VALUES(N'Underscore language code',0);
END TRY BEGIN CATCH INSERT @Errors VALUES(N'Underscore language code',ERROR_NUMBER()); END CATCH;
SELECT TestCase,ErrorNumber FROM @Errors ORDER BY TestCase;

In my October 2, 2026 SQL Server 2025 run, all twelve rows came out as expected and all seven writes were rejected. The French name ended with code point 233 and used six storage bytes. The Japanese name had code point 33590 and used two storage bytes.

The tab and nonbreaking-space probes are deliberately accepted by this narrow LEN policy. Their code points and byte lengths remain visible in the results. If the interface rejects those characters, validate them with an explicitly broader rule.

INSERT dbo.DemoTranslations VALUES(2,'fr-fr',NCHAR(9)),(3,'fr-fr',NCHAR(160));
SELECT ProductId,LanguageCode,UNICODE(NameText) AS FirstCodePoint,LEN(NameText) AS NameLength,
DATALENGTH(NameText) AS NameBytes FROM dbo.DemoTranslations WHERE ProductId IN(2,3) AND LanguageCode='fr-fr' ORDER BY ProductId;

A stored empty string would be a value rather than NULL. COALESCE alone would not turn it into the base name. This design prevents ordinary empty names instead of silently redefining missing data during every read.

Separate Sorting From Translation

Language selection and collation serve different purposes. Selecting fr-fr chooses a stored French label. It does not change every comparison or sort into a French-language policy.

The language-tag collation controls key comparison, not translation of the name. A display sort needs its own approved collation and stable tie-breaker. Static SQL requires a literal collation name, rather than a runtime collation variable.

Keep language parameters in procedures and cache keys. Searching across translations also needs a declared language scope and separate workload evidence. This small display lookup establishes no search or performance benchmark.

Clean Up

Drop the two demo tables when you are done.

DROP TABLE dbo.DemoTranslations;
DROP TABLE dbo.DemoProducts;

Keep the product identity stable across languages, and make the fallback explicit.

A translated name is not a new product, it is another way to describe the same one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.