Abstraction for Multilingual Content

[dtdesign]

There are multiple different implementations for multilingual content and they are all either flawed to some extent or quite limited or both. Some store there values in phrases, others use separate tables but are inconsistent on how the ids are mapped. Furthermore being able to mix monolingual and multilingual content creates a lot of issues when sorting, filtering or generally trying to work with those values because it involves some kind of magic, for example, the value could be a plain value or the name of a phrase.

The only solution forward is a consistent abstraction that handles this in a uniform way that does not rely on phrases (which was a stop-gap solution in itself) and provides convenient helper methods to work with them. It needs to solve the following problems:

• Any localized value must be stored separately regardless of the number of available languages.
• Monolingual content must be represented as a multilingual content with only a single localization.
• Displaying, sorting and filtering by values must be deterministic.
  • Available localizations can be inconsistent, the value must be determined in this order:
    • Requested language.
    • Default language.
    • Values for the language with the lowest languageID.

Determining the values can be done through a sub select using either CASE … THEN or by assigning the languageID a value by preference. The latter could be achieved by setting -2 for preferred language, -1 for default language and the actual languageID for everything else followed by then selecting the lowest value.

This requires extra tables for any such content and should be entirely managed like it is already the case with the *_search_index tables. objectID and languageID need to be fixed and all other columns could be represented through a list of IDatabaseTableColumn. Additionally this would set up a foreign key for both the languageID as well as the objectID with the latter referencing the original object.

We need to explore if this is feasible through specialized helper methods to avoid API consumers having to write custom queries to work with these values.