Web Application Translation, methods and tools

Question

I've developed a web application. It needs to be translated to languages other than English in the future, and ideally the translators shouldn't need to know HTML/JS/C++ to provide the translation. The server side of the web application is written in C++ and the majority of the localised text is in the HTML files.

My question is: What approaches are there to translating web applications? -

• Are there any existing tools that would enable a translator who doesn't understand HTML to translate a site?
• Should I write an application that extracts the localised text from a html file and can re-substitute translated text?
• Do you just provide the html file to your translators to be localised?

I'm aware the question isn't strictly programming related but the solution may involve programming and it may require some software engineering.

Answer 1

The best and most professional way is to provide a web interface for translation agencies. This way you can outsource the work anywhere you please.

Also think of embedding the UI into your development process. You add new textual resources to your application, they are automatically published in this UI. Agencies perform translation and mark this particular item as translated. The build picks up the already translated resources and substitutes stubs or default texts with those translations. If a string is reported as being incorrectly translated, you mark it to send for repeated translation again.

This is easily done when this is conceived from the beginning of the development. It's not exactly a feature you can easily add later, but it is of course possible.

For this to work, you need to store every translatable piece of text somewhere special. Either you keep them in a database, store in some XML file, you need two things:

• Integrate those resources into your development process. For instance, when you compile a project, the resources are picked up from that storage and put into their proper places in the code, markup or whatever you do.

• Provide external access to that storage. Add/delete rights only to yourself, add/modify translations for existing items for external users.

Answer 2

First of all, keep every translatable unit of text in a uniquely identified div tag on each web page. Store the content in a database table that has the div id and the language id as the key and a text field for the content. Allow basic HTML markup, but no script or styling.

Have a page on your system to set language preferences, and store it on the user's profile, or in a long-lasting cookie in the user's browser.

Have your web application render the unique div or span tags from the database based on the language preference.

You can have a separate application that displays the default text (e.g. English) and then has a text area for a translator to type in a translation. Once it is typed in, that div tag can be rendered in the language of the user's choice.

Another big thing to watch for is that some content is layout-independent, such as paragraphs, blog postings, etc. Other content is very layout-dependent such as menu items, headers, etc. Also, some languages such as Hebrew and Arabic go from right to left instead of left to right. That may affect how the translated content is laid out. In such situations, you may want a separate layout template for those languages, and the selection of the template would be driven by the language preference.

Answer 3

Having some experience in localization of the applications, I can tell you the following:

• Any translator you can rely on, will not have problems with HTML (assuming that the translation would not break the design)
• Most professional translators use translation memory applications (i.e., Transit, Trados) that can parse many document formats (XML, HTML, PDF, .DOC, etc.) and separate markup from content. They will deliver a translated copy in the same format as original.
• All messages to be translated that are used within your programming code should be isolated in resource bundles. Almost all popular web-application frameworks have the corresponding means. The bundles are usually just plain text files with key/value pairs. Translator should not see the code.
• Messages in resource bundles can be formatting strings for printf-like functions. In that case, you should document the expected 'fillers'.
• When you provide resource bundles to translators, be sure to attach instructions how to get the texts in the application interface, so that the translator would know the context of the given message.
• If any labels should not exceed a length, you should inform about it in advance.
• If the application uses company-specific terminology, you should provide a glossary, so that the translation would be consistent.
• Do your best to get rid of texts on images. Those will be your head ache.
• If you translate from English, you might face the necessity to introduce additional logic to cover the grammatical features of the target language (correct case, gender)
• it is very smart to store user manual text and similar texts in simple XML format (a subset of XHTML, DOCBOOK) and apply an XSL transformation for resulting HTML. It allows easily outsource the translation and validate the format of the document.

The list is certainly not web-application specific.

Answer 4

As the above individuals have mentioned - string externalization is in most respects the core concept in Internationalization/Localization. Your choice of bundles depends on platform - for C getText is a popular option.

While you could write a translation frontend, if you're mostly translating application UI (as opposed to content) then the above approach, combined with either a desktop app (there are many, you can just ship translators the String bundles, and they can edit and return) or, my favorite these days, Pootle (which is a web front-end, and can commit translations direct to SCM) can provide friendly frontends

Answer 5

            /****** Object:  Table [dbo].[LangInfo]    Script Date: 03/29/2010 14:58:37 ******/
            IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[LangInfo]') AND type in (N'U'))
            DROP TABLE [dbo].[LangInfo]
            GO

            /****** Object:  Table [dbo].[LangInfo]    

            Script Date: 03/29/2010 14:58:37 ******/
            SET ANSI_NULLS ON
            GO

            SET QUOTED_IDENTIFIER ON
            GO

            CREATE TABLE [dbo].[LangInfo](
                [LangInfoId] [int] IDENTITY(1,1) NOT NULL,
                [CultureName] [varchar](10) NOT NULL,
                [DisplayName] [varchar](50) NULL,
                [ISO_639x_Value] [nchar](6) NULL,
                [CultureCode] [nvarchar](10) NULL,
                [CollationName] [varchar](50) NULL,
                [IsEnabledInApp] [bit] NULL,
                [CultureNameU] [varchar](10) NOT NULL,
             CONSTRAINT [PK_CultureInfo] PRIMARY KEY CLUSTERED 
            (
                [LangInfoId] ASC
            )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
            ) ON [PRIMARY]

            /*
            <doc> 
            Stores culture / country info and models culture formatting 
            </doc>
            */

            GO


    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    GO

    SET IDENTITY_INSERT [dbo].[LangInfo] ON;
    BEGIN TRANSACTION;
    INSERT INTO [dbo].[LangInfo]([LangInfoId], [CultureName], [DisplayName], [ISO_639x_Value], [CultureCode], [CollationName], [IsEnabledInApp], [CultureNameU])
    SELECT 1, N'af-ZA', N'Afrikaans - South Africa', N'0x0436', N'AFK', NULL, 0, N'af_ZA' UNION ALL
    SELECT 2, N'sq-AL', N'Albanian - Albania', N'0x041C', N'SQI', NULL, 0, N'sq_AL' UNION ALL
    SELECT 3, N'ar-DZ', N'Arabic - Algeria', N'0x1401', N'ARG', NULL, 0, N'ar_DZ' UNION ALL
    SELECT 4, N'ar-BH', N'Arabic - Bahrain', N'0x3C01', N'ARH', NULL, 0, N'ar_BH' UNION ALL
    SELECT 5, N'ar-EG', N'Arabic - Egypt', N'0x0C01', N'ARE', NULL, 0, N'ar_EG' UNION ALL
    SELECT 6, N'ar-IQ', N'Arabic - Iraq', N'0x0801', N'ARI', NULL, 0, N'ar_IQ' UNION ALL
    SELECT 7, N'ar-JO', N'Arabic - Jordan', N'0x2C01', N'ARJ', NULL, 0, N'ar_JO' UNION ALL
    SELECT 8, N'ar-KW', N'Arabic - Kuwait', N'0x3401', N'ARK', NULL, 0, N'ar_KW' UNION ALL
    SELECT 9, N'ar-LB', N'Arabic - Lebanon', N'0x3001', N'ARB', NULL, 0, N'ar_LB' UNION ALL
    SELECT 10, N'ar-LY', N'Arabic - Libya', N'0x1001', N'ARL', NULL, 0, N'ar_LY' UNION ALL
    SELECT 11, N'ar-MA', N'Arabic - Morocco', N'0x1801', N'ARM', NULL, 0, N'ar_MA' UNION ALL
    SELECT 12, N'ar-OM', N'Arabic - Oman', N'0x2001', N'ARO', NULL, 0, N'ar_OM' UNION ALL
    SELECT 13, N'ar-QA', N'Arabic - Qatar', N'0x4001', N'ARQ', NULL, 0, N'ar_QA' UNION ALL
    SELECT 14, N'ar-SA', N'Arabic - Saudi Arabia', N'0x0401', N'ARA', NULL, 0, N'ar_SA' UNION ALL
    SELECT 15, N'ar-SY', N'Arabic - Syria', N'0x2801', N'ARS', NULL, 0, N'ar_SY' UNION ALL
    SELECT 16, N'ar-TN', N'Arabic - Tunisia', N'0x1C01', N'ART', NULL, 0, N'ar_TN' UNION ALL
    SELECT 17, N'ar-AE', N'Arabic - United Arab Emirates', N'0x3801', N'ARU', NULL, 0, N'ar_AE' UNION ALL
    SELECT 18, N'ar-YE', N'Arabic - Yemen', N'0x2401', N'ARY', NULL, 0, N'ar_YE' UNION ALL
    SELECT 19, N'hy-AM', N'Armenian - Armenia', N'0x042B', N' ', NULL, 0, N'hy_AM' UNION ALL
    SELECT 20, N'Cy-az-AZ', N'Azeri (Cyrillic) - Azerbaijan', N'0x082C', N' ', NULL, 0, N'Cy_az_AZ' UNION ALL
    SELECT 21, N'Lt-az-AZ', N'Azeri (Latin) - Azerbaijan', N'0x042C', N' ', NULL, 0, N'Lt_az_AZ' UNION ALL
    SELECT 22, N'eu-ES', N'Basque - Basque', N'0x042D', N'EUQ', NULL, 0, N'eu_ES' UNION ALL
    SELECT 23, N'be-BY', N'Belarusian - Belarus', N'0x0423', N'BEL', NULL, 0, N'be_BY' UNION ALL
    SELECT 24, N'bg-BG', N'Bulgarian - Bulgaria', N'0x0402', N'BGR', NULL, 1, N'bg_BG' UNION ALL
    SELECT 25, N'ca-ES', N'Catalan - Catalan', N'0x0403', N'CAT', NULL, 0, N'ca_ES' UNION ALL
    SELECT 26, N'zh-CN', N'Chinese - China', N'0x0804', N'CHS', NULL, 0, N'zh_CN' UNION ALL
    SELECT 27, N'zh-HK', N'Chinese - Hong Kong SAR', N'0x0C04', N'ZHH', NULL, 0, N'zh_HK' UNION ALL
    SELECT 28, N'zh-MO', N'Chinese - Macau SAR', N'0x1404', N' ', NULL, 0, N'zh_MO' UNION ALL
    SELECT 29, N'zh-SG', N'Chinese - Singapore', N'0x1004', N'ZHI', NULL, 0, N'zh_SG' UNION ALL
    SELECT 30, N'zh-TW', N'Chinese - Taiwan', N'0x0404', N'CHT', NULL, 0, N'zh_TW' UNION ALL
    SELECT 31, N'zh-CHS', N'Chinese (Simplified)', N'0x0004', N' ', NULL, 0, N'zh_CHS' UNION ALL
    SELECT 32, N'zh-CHT', N'Chinese (Traditional)', N'0x7C04', N' ', NULL, 0, N'zh_CHT' UNION ALL
    SELECT 33, N'hr-HR', N'Croatian - Croatia', N'0x041A', N'HRV', NULL, 0, N'hr_HR' UNION ALL
    SELECT 34, N'cs-CZ', N'Czech - Czech Republic', N'0x0405', N'CSY', NULL, 0, N'cs_CZ' UNION ALL
    SELECT 35, N'da-DK', N'Danish - Denmark', N'0x0406', N'DAN', NULL, 0, N'da_DK' UNION ALL
    SELECT 36, N'div-MV', N'Dhivehi - Maldives', N'0x0465', N' ', NULL, 0, N'div_MV' UNION ALL
    SELECT 37, N'nl-BE', N'Dutch - Belgium', N'0x0813', N'NLB', NULL, 0, N'nl_BE' UNION ALL
    SELECT 38, N'nl-NL', N'Dutch - The Netherlands', N'0x0413', N' ', NULL, 0, N'nl_NL' UNION ALL
    SELECT 39, N'en-AU', N'English - Australia', N'0x0C09', N'ENA', NULL, 0, N'en_AU' UNION ALL
    SELECT 40, N'en-BZ', N'English - Belize', N'0x2809', N'ENL', NULL, 0, N'en_BZ' UNION ALL
    SELECT 41, N'en-CA', N'English - Canada', N'0x1009', N'ENC', NULL, 0, N'en_CA' UNION ALL
    SELECT 42, N'en-CB', N'English - Caribbean', N'0x2409', N' ', NULL, 0, N'en_CB' UNION ALL
    SELECT 43, N'en-IE', N'English - Ireland', N'0x1809', N'ENI', NULL, 0, N'en_IE' UNION ALL
    SELECT 44, N'en-JM', N'English - Jamaica', N'0x2009', N'ENJ', NULL, 0, N'en_JM' UNION ALL
    SELECT 45, N'en-NZ', N'English - New Zealand', N'0x1409', N'ENZ', NULL, 0, N'en_NZ' UNION ALL
    SELECT 46, N'en-PH', N'English - Philippines', N'0x3409', N' ', NULL, 0, N'en_PH' UNION ALL
    SELECT 47, N'en-ZA', N'English - South Africa', N'0x1C09', N'ENS', NULL, 0, N'en_ZA' UNION ALL
    SELECT 48, N'en-TT', N'English - Trinidad and Tobago', N'0x2C09', N'ENT', NULL, 0, N'en_TT' UNION ALL
    SELECT 49, N'en-GB', N'English - United Kingdom', N'0x0809', N'ENG', NULL, 0, N'en_GB' UNION ALL
    SELECT 50, N'en-US', N'English - United States', N'0x0409', N'ENU', NULL, 1, N'en_US'
    COMMIT;
    RAISERROR (N'[dbo].[LangInfo]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
    GO

    BEGIN TRANSACTION;
    INSERT INTO [dbo].[LangInfo]([LangInfoId], [CultureName], [DisplayName], [ISO_639x_Value], [CultureCode], [CollationName], [IsEnabledInApp], [CultureNameU])
    SELECT 51, N'en-ZW', N'English - Zimbabwe', N'0x3009', N' ', NULL, 0, N'en_ZW' UNION ALL
    SELECT 52, N'et-EE', N'Estonian - Estonia', N'0x0425', N'ETI', NULL, 0, N'et_EE' UNION ALL
    SELECT 53, N'fo-FO', N'Faroese - Faroe Islands', N'0x0438', N'FOS', NULL, 0, N'fo_FO' UNION ALL
    SELECT 54, N'fa-IR', N'Farsi - Iran', N'0x0429', N'FAR', NULL, 0, N'fa_IR' UNION ALL
    SELECT 55, N'fi-FI', N'Finnish - Finland', N'0x040B', N'FIN', N'Finnish_Swedish_CI_AS', 1, N'fi_FI' UNION ALL
    SELECT 56, N'fr-BE', N'French - Belgium', N'0x080C', N'FRB', NULL, 0, N'fr_BE' UNION ALL
    SELECT 57, N'fr-CA', N'French - Canada', N'0x0C0C', N'FRC', NULL, 0, N'fr_CA' UNION ALL
    SELECT 58, N'fr-FR', N'French - France', N'0x040C', N' ', NULL, 0, N'fr_FR' UNION ALL
    SELECT 59, N'fr-LU', N'French - Luxembourg', N'0x140C', N'FRL', NULL, 0, N'fr_LU' UNION ALL
    SELECT 60, N'fr-MC', N'French - Monaco', N'0x180C', N' ', NULL, 0, N'fr_MC' UNION ALL
    SELECT 61, N'fr-CH', N'French - Switzerland', N'0x100C', N'FRS', NULL, 0, N'fr_CH' UNION ALL
    SELECT 62, N'gl-ES', N'Galician - Galician', N'0x0456', N' ', NULL, 0, N'gl_ES' UNION ALL
    SELECT 63, N'ka-GE', N'Georgian - Georgia', N'0x0437', N' ', NULL, 0, N'ka_GE' UNION ALL
    SELECT 64, N'de-AT', N'German - Austria', N'0x0C07', N'DEA', NULL, 0, N'de_AT' UNION ALL
    SELECT 65, N'de-DE', N'German - Germany', N'0x0407', N' ', NULL, 0, N'de_DE' UNION ALL
    SELECT 66, N'de-LI', N'German - Liechtenstein', N'0x1407', N'DEC', NULL, 0, N'de_LI' UNION ALL
    SELECT 67, N'de-LU', N'German - Luxembourg', N'0x1007', N'DEL', NULL, 0, N'de_LU' UNION ALL
    SELECT 68, N'de-CH', N'German - Switzerland', N'0x0807', N'DES', NULL, 0, N'de_CH' UNION ALL
    SELECT 69, N'el-GR', N'Greek - Greece', N'0x0408', N'ELL', NULL, 0, N'el_GR' UNION ALL
    SELECT 70, N'gu-IN', N'Gujarati - India', N'0x0447', N' ', NULL, 0, N'gu_IN' UNION ALL
    SELECT 71, N'he-IL', N'Hebrew - Israel', N'0x040D', N'HEB', NULL, 0, N'he_IL' UNION ALL
    SELECT 72, N'hi-IN', N'Hindi - India', N'0x0439', N'HIN', NULL, 0, N'hi_IN' UNION ALL
    SELECT 73, N'hu-HU', N'Hungarian - Hungary', N'0x040E', N'HUN', NULL, 0, N'hu_HU' UNION ALL
    SELECT 74, N'is-IS', N'Icelandic - Iceland', N'0x040F', N'ISL', NULL, 0, N'is_IS' UNION ALL
    SELECT 75, N'id-ID', N'Indonesian - Indonesia', N'0x0421', N' ', NULL, 0, N'id_ID' UNION ALL
    SELECT 76, N'it-IT', N'Italian - Italy', N'0x0410', N' ', NULL, 0, N'it_IT' UNION ALL
    SELECT 77, N'it-CH', N'Italian - Switzerland', N'0x0810', N'ITS', NULL, 0, N'it_CH' UNION ALL
    SELECT 78, N'ja-JP', N'Japanese - Japan', N'0x0411', N'JPN', NULL, 0, N'ja_JP' UNION ALL
    SELECT 79, N'kn-IN', N'Kannada - India', N'0x044B', N' ', NULL, 0, N'kn_IN' UNION ALL
    SELECT 80, N'kk-KZ', N'Kazakh - Kazakhstan', N'0x043F', N' ', NULL, 0, N'kk_KZ' UNION ALL
    SELECT 81, N'kok-IN', N'Konkani - India', N'0x0457', N' ', NULL, 0, N'kok_IN' UNION ALL
    SELECT 82, N'ko-KR', N'Korean - Korea', N'0x0412', N'KOR', NULL, 0, N'ko_KR' UNION ALL
    SELECT 83, N'ky-KZ', N'Kyrgyz - Kazakhstan', N'0x0440', N' ', NULL, 0, N'ky_KZ' UNION ALL
    SELECT 84, N'lv-LV', N'Latvian - Latvia', N'0x0426', N'LVI', NULL, 0, N'lv_LV' UNION ALL
    SELECT 85, N'lt-LT', N'Lithuanian - Lithuania', N'0x0427', N'LTH', NULL, 0, N'lt_LT' UNION ALL
    SELECT 86, N'mk-MK', N'Macedonian (FYROM)', N'0x042F', N'MKD', NULL, 0, N'mk_MK' UNION ALL
    SELECT 87, N'ms-BN', N'Malay - Brunei', N'0x083E', N' ', NULL, 0, N'ms_BN' UNION ALL
    SELECT 88, N'ms-MY', N'Malay - Malaysia', N'0x043E', N' ', NULL, 0, N'ms_MY' UNION ALL
    SELECT 89, N'mr-IN', N'Marathi - India', N'0x044E', N' ', NULL, 0, N'mr_IN' UNION ALL
    SELECT 90, N'mn-MN', N'Mongolian - Mongolia', N'0x0450', N' ', NULL, 0, N'mn_MN' UNION ALL
    SELECT 91, N'nb-NO', N'Norwegian (Bokmål) - Norway', N'0x0414', N' ', NULL, 0, N'nb_NO' UNION ALL
    SELECT 92, N'nn-NO', N'Norwegian (Nynorsk) - Norway', N'0x0814', N' ', NULL, 0, N'nn_NO' UNION ALL
    SELECT 93, N'pl-PL', N'Polish - Poland', N'0x0415', N'PLK', NULL, 0, N'pl_PL' UNION ALL
    SELECT 94, N'pt-BR', N'Portuguese - Brazil', N'0x0416', N'PTB', NULL, 0, N'pt_BR' UNION ALL
    SELECT 95, N'pt-PT', N'Portuguese - Portugal', N'0x0816', N' ', NULL, 0, N'pt_PT' UNION ALL
    SELECT 96, N'pa-IN', N'Punjabi - India', N'0x0446', N' ', NULL, 0, N'pa_IN' UNION ALL
    SELECT 97, N'ro-RO', N'Romanian - Romania', N'0x0418', N'ROM', NULL, 0, N'ro_RO' UNION ALL
    SELECT 98, N'ru-RU', N'Russian - Russia', N'0x0419', N'RUS', NULL, 0, N'ru_RU' UNION ALL
    SELECT 99, N'sa-IN', N'Sanskrit - India', N'0x044F', N' ', NULL, 0, N'sa_IN' UNION ALL
    SELECT 100, N'Cy-sr-SP', N'Serbian (Cyrillic) - Serbia', N'0x0C1A', N' ', NULL, 0, N'Cy_sr_SP'
    COMMIT;
    RAISERROR (N'[dbo].[LangInfo]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;
    GO

    BEGIN TRANSACTION;
    INSERT INTO [dbo].[LangInfo]([LangInfoId], [CultureName], [DisplayName], [ISO_639x_Value], [CultureCode], [CollationName], [IsEnabledInApp], [CultureNameU])
    SELECT 101, N'Lt-sr-SP', N'Serbian (Latin) - Serbia', N'0x081A', N' ', NULL, 0, N'Lt_sr_SP' UNION ALL
    SELECT 102, N'sk-SK', N'Slovak - Slovakia', N'0x041B', N'SKY', NULL, 0, N'sk_SK' UNION ALL
    SELECT 103, N'sl-SI', N'Slovenian - Slovenia', N'0x0424', N'SLV', NULL, 0, N'sl_SI' UNION ALL
    SELECT 104, N'es-AR', N'Spanish - Argentina', N'0x2C0A', N'ESS', NULL, 0, N'es_AR' UNION ALL
    SELECT 105, N'es-BO', N'Spanish - Bolivia', N'0x400A', N'ESB', NULL, 0, N'es_BO' UNION ALL
    SELECT 106, N'es-CL', N'Spanish - Chile', N'0x340A', N'ESL', NULL, 0, N'es_CL' UNION ALL
    SELECT 107, N'es-CO', N'Spanish - Colombia', N'0x240A', N'ESO', NULL, 0, N'es_CO' UNION ALL
    SELECT 108, N'es-CR', N'Spanish - Costa Rica', N'0x140A', N'ESC', NULL, 0, N'es_CR' UNION ALL
    SELECT 109, N'es-DO', N'Spanish - Dominican Republic', N'0x1C0A', N'ESD', NULL, 0, N'es_DO' UNION ALL
    SELECT 110, N'es-EC', N'Spanish - Ecuador', N'0x300A', N'ESF', NULL, 0, N'es_EC' UNION ALL
    SELECT 111, N'es-SV', N'Spanish - El Salvador', N'0x440A', N'ESE', NULL, 0, N'es_SV' UNION ALL
    SELECT 112, N'es-GT', N'Spanish - Guatemala', N'0x100A', N'ESG', NULL, 0, N'es_GT' UNION ALL
    SELECT 113, N'es-HN', N'Spanish - Honduras', N'0x480A', N'ESH', NULL, 0, N'es_HN' UNION ALL
    SELECT 114, N'es-MX', N'Spanish - Mexico', N'0x080A', N'ESM', NULL, 0, N'es_MX' UNION ALL
    SELECT 115, N'es-NI', N'Spanish - Nicaragua', N'0x4C0A', N'ESI', NULL, 0, N'es_NI' UNION ALL
    SELECT 116, N'es-PA', N'Spanish - Panama', N'0x180A', N'ESA', NULL, 0, N'es_PA' UNION ALL
    SELECT 117, N'es-PY', N'Spanish - Paraguay', N'0x3C0A', N'ESZ', NULL, 0, N'es_PY' UNION ALL
    SELECT 118, N'es-PE', N'Spanish - Peru', N'0x280A', N'ESR', NULL, 0, N'es_PE' UNION ALL
    SELECT 119, N'es-PR', N'Spanish - Puerto Rico', N'0x500A', N'ES', NULL, 0, N'es_PR' UNION ALL
    SELECT 120, N'es-ES', N'Spanish - Spain', N'0x0C0A', N' ', NULL, 0, N'es_ES' UNION ALL
    SELECT 121, N'es-UY', N'Spanish - Uruguay', N'0x380A', N'ESY', NULL, 0, N'es_UY' UNION ALL
    SELECT 122, N'es-VE', N'Spanish - Venezuela', N'0x200A', N'ESV', NULL, 0, N'es_VE' UNION ALL
    SELECT 123, N'sw-KE', N'Swahili - Kenya', N'0x0441', N' ', NULL, 0, N'sw_KE' UNION ALL
    SELECT 124, N'sv-FI', N'Swedish - Finland', N'0x081D', N'SVF', NULL, 0, N'sv_FI' UNION ALL
    SELECT 125, N'sv-SE', N'Swedish - Sweden', N'0x041D', N' ', NULL, 0, N'sv_SE' UNION ALL
    SELECT 126, N'syr-SY', N'Syriac - Syria', N'0x045A', N' ', NULL, 0, N'syr_SY' UNION ALL
    SELECT 127, N'ta-IN', N'Tamil - India', N'0x0449', N' ', NULL, 0, N'ta_IN' UNION ALL
    SELECT 128, N'tt-RU', N'Tatar - Russia', N'0x0444', N' ', NULL, 0, N'tt_RU' UNION ALL
    SELECT 129, N'te-IN', N'Telugu - India', N'0x044A', N' ', NULL, 0, N'te_IN' UNION ALL
    SELECT 130, N'th-TH', N'Thai - Thailand', N'0x041E', N'THA', NULL, 0, N'th_TH' UNION ALL
    SELECT 131, N'tr-TR', N'Turkish - Turkey', N'0x041F', N'TRK', NULL, 0, N'tr_TR' UNION ALL
    SELECT 132, N'uk-UA', N'Ukrainian - Ukraine', N'0x0422', N'UKR', NULL, 0, N'uk_UA' UNION ALL
    SELECT 133, N'ur-PK', N'Urdu - Pakistan', N'0x0420', N'URD', NULL, 0, N'ur_PK' UNION ALL
    SELECT 134, N'Cy-uz-UZ', N'Uzbek (Cyrillic) - Uzbekistan', N'0x0843', N' ', NULL, 0, N'Cy_uz_UZ' UNION ALL
    SELECT 135, N'Lt-uz-UZ', N'Uzbek (Latin) - Uzbekistan', N'0x0443', N' ', NULL, 0, N'Lt_uz_UZ' UNION ALL
    SELECT 136, N'vi-VN', N'Vietnamese - Vietnam', N'0x042A', N'VIT', NULL, 0, N'vi_VN'
    COMMIT;
    RAISERROR (N'[dbo].[LangInfo]: Insert Batch: 3.....Done!', 10, 1) WITH NOWAIT;
    GO

    SET IDENTITY_INSERT [dbo].[LangInfo] OFF;




--Since Developers derive from Humans ... 

/****** Object:  Table [dbo].[Msg]    
Script Date: 03/31/2010     21:07:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID    
(N'[dbo].[Msg]') AND type in (N'U'))
DROP TABLE [dbo].[Msg]
GO

/****** Object:  Table [dbo].[Msg]    
Script Date: 03/31/2010 21:07:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Msg](
[MsgId] [int] IDENTITY(1,1) NOT NULL,
[Txt][nvarchar](max) NOT NULL , 
 -- This is the Developers language ; )
 -- it is based , but not equal to the humans natural English language 
[Description] [varchar](max) NULL,
[IsGlobal] [bit] NULL,
[Txt_en_Us] [nvarchar](max) NULL,
[Txt_fi_Fi] [nvarchar](max) NULL , 
 CONSTRAINT [PK_Msg] PRIMARY KEY CLUSTERED 
(
    [MsgId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 -- SET THE COLLATION FOR THE FINNISHI LANGUAGE
 ALTER  TABLE [dbo].[Msg]
 ALTER COLUMN [Txt_fi_FI] [nvarchar](4000)
 COLLATE Finnish_Swedish_CS_AI


 /* 
 <doc> Stores all the messages in the Application. When adding     
 new language a new column must be added </doc>
 */
 GO


 /****** Object:  Index [IX_Msg]    
Script Date: 03/31/2010 21:07:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Msg] ON [dbo].[Msg] 
(
    [MsgId] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
 ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON     [PRIMARY]
GO


--soon to come --> sql to get dynamically the vals

-- Get the list of all the English words 
-- and insert it .. 

    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aA') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aH') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aI') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aN') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aU') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aW') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aX') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aa') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ab') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ac') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ad') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ae') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'af') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ag') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ah') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ai') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aj') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ak') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'al') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'am') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'an') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ao') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ap') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'aq') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'ar') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'as') ;
    INSERT INTO [dbo].[Msg] ([Txt]) VALUES ( N'at') ;



DECLARE @langCode nvarchar(8) 
set @langCode = 'en_US'

-- GET THE MESSAGES ONLY FOR THE DESIRED LANGUA
DECLARE @StrSqlCode nvarchar(max) 
set @StrSqlCode = 'SELECT Txt' + '_' + @langCode + ' from Msg      
where IsGlobal = 1'

exec ( @StrSqlCode) 

Answer 6

Most of the commercial websites maintain different webpage for different langues which obviously is not a feasible solution. Translation of webpage into an all together different language can be of two type : UI level and Functional Level.

At UI level , you translate the label text, button text, table Header , DropDown and menu options into their corresponding text in different language.

Functional level is where you have to work a bit harder by providing proper translation of datetime and currency fields. For e.g. 100 $ is not same as 100 Euro (A blunder I committed in my application!!)....

I used jQuery DOM manipulation technique to translate the text depending upon the selection made by the user. I stored all the text and their corresponding translation into Database.Also using asp.net profiles , I maintained users preference during login/logout operations.

Also in your application give user explicit option to select new language rather than relying on browser language settings.