Data localization in SQL Server with a GUID or ...?

Question

I need to make a database that is highly localized. For almost all entities, I need a translation in 5+ languages. Some objects even require and an additional resource is localized (for example, images that I entered as paths).

Now the question is:

1: LOOKUP PER ENTITY / TABLE TABLE (view of a bloated chart?)

should i create a localized localization localization table for each table. I need localized values ​​(and use standard int / bigint PK elements for elements) Like here:

MYITEMS ------- - MyItemId BIGINT PK - MyItemPrice DECIMAL MYITEMLOCALIZED --------------- - CPK_MyItemId BIGINT FK - CPK_LanguageCode NCHAR - LocalizedName NVARCHAR - LocalizedResourcePath NVARCHAR CUSTOMERS --------- - CustomerId BIGINT PK - CustomerName NVARCHAR CUSTOMERLOCALIZED --------------- - CPK_CustomerId BIGINT FK - CPK_LanguageCode NCHAR - LocalizedName NVARCHAR - LocalizedResourcePath NVARCHAR 

or

2: GUIDS WITH SINGLE LOOKUP LOCALIZATION TABLE (using heavy manual?)

you should use the GUID as PK, and then just use the same name and localization table of the same resource.

 MYITEMS ------- - MyItemId uniqueidentifier PK - MyItemPrice DECIMAL CUSTOMERS --------- - CustomerId uniqueidentifier PK - CustomerName NVARCHAR LOCALIZED --------------- - CPK_ElementGuid uniqueidentifier FK - CPK_LanguageCode NCHAR - LocalizedValue NVARCHAR - LocalizedResourcePath NVARCHAR 

3: SINGLE VIEW, BUT ONLY FOR ACCESS TO LOCALIZATION (best of two worlds?)

Should I use regular int / bigint PK and then add a GUID column for each column that I need and store localized values ​​in one localization lookup table.

 MYITEMS ------- - MyItemId BIGINT PK - MyItemPrice DECIMAL - ItemNameLocalizationGuid uniqueidentifier(GUID) - ItemPictureLocalizationGuid uniqueidentifier(GUID) CUSTOMERS --------- - CustomerId BIGINT PK - CustomerName NVARCHAR - CustomeerNameLocalizationGuid uniqueidentifier(GUID) LOCALIZED --------------- - CPK_ElementGuid uniqueidentifier FK - CPK_LanguageCode NCHAR - LocalizedValue NVARCHAR 

4: SHOOTING TABLE RETURNING LOCALIZATION IDENTIFICATION (go back and forth?)

Should I create tables without instructions, but store the localization identifier in the parent table?

Like here:

 MYITEMS ------- - MyItemId BIGINT PK - MyItemPrice DECIMAL - MyItemNameLocalizedId BIGINT CUSTOMERS --------- - CustomerId BIGINT PK - CustomerName NVARCHAR - CustomerGenderLocalizedId BIGINT LOCALIZED --------------- - LocalizationId BIGINT PK - CustomerId BIGINT FK - LanguageCode NCHAR - LocalizedName NVARCHAR - LocalizedResourcePath NVARCHAR 

---

If I use the GUID as the PK that I read, I will incur tremendous performance and a fine for the size of the data, but I will also instantly consider the uniqueness of the elements on the servers, dbs ...

Answer 1

First of all, I highly recommend using the existing standard for localization identifiers - do not reinvent another system! Use standard ISO-639 codes for the language, for example. "en" for English, "fr" for French, etc.

See Wikipedia for a list of all specific codes.

Secondly, in my experience and my opinion, I would use a table of languages ​​for each object.

Usually we have a "system name" in the main table, for example. the text is in English, and then we have the table "(entity) _TX" for textual representation in different languages.

Something like that:

  TABLE CustomerType CustomerTypeID INT IDENTITY(1,1) PK CustomerTypeName VARCHAR(100) -- English "system" name, eg "Gold customer" TABLE CustomerType_TX CustomerTypeID INT LanguageID CHAR(2) -- ISO-639 codes CustomerTypeText VARCHAR(200) -- translated texts 

For me, this is clearer and more explicit and more “intuitive” than having a single GUID-based coding scheme.