Data in Multiple Languages in the same table

[TarunKNayak]

Hi All,
I searched through the archieve but could not get the solution. I want the text data to be stored in different languages in different columns of a table. Kindly describe the procedure in detail.
I do not know how to use unicode to store data. A detailed description would help me a lot.

Thanks,
Tarun tknayak@hotmail.com

 

[chiph]

Hello Tarun -
It involves using two tables, plus a LanguageID table.

One stores the language-independent values, the other stores the language text. Here's an example for a Location table:

tbl_LanguageID 
==================
LangID       int       identity, pkey
Description  nvarchar(50)
ISOCode      nchar(3)

tbl_Location
==================
LocationID   int       identity, pkey
Description  nvarchar(50)
TimeZone     int
CreatedOn    datetime

tbl_LocationLang
==================
LangID       int       pkey
LocationID   int       pkey
DisplayText  nvarchar(50)
City         nvarchar(30)
State        nvarchar(50)
PostalCode   nchar(20)

Every time you need a Location you have to join the two tables on the LocationID and use the LangID in the Where clause:

SELECT A.Description,
  B.DisplayText,
  B.City,
  B.State,
  B.PostalCode
FROM tbl_Location A
  INNER JOIN tbl_LocationLang B ON
    A.LocationID = B.Location
WHERE A.LocationID = 12
  AND B.LangID = 2

An even better idea would be to use the Windows Locale ID's (LCIDs) instead of a Language ID, as that allows you to keep standard German separate from Swiss German and US English separate from UK, Canadian, and HongKong English.

Hope this helps.
Chip H.

PS. In case anyone is wondering why you'd have a language-indendent location table, consider "Munich" and "München" -- two names for the same city.