Localization of data

[valadas]

I don't remember from the presentation if this was already somehow supported but it would be nice to have localized data.

I did not have time yet to dig in into how it's done now for field names, but I guess there is some "meta-data" about the created tables stored somewhere, so if we can have a local field in that meta-data, that would allow us to have localized field names for the form/display UI.

For the data, at a bare minimum, we need to be able to duplicate each row and have a locale field on it. If that is blank (it probably needs to be nullable to cover the scenario of a non-localized site becoming localized in the future), then I think if it's null it represents the site default language, then for other locales, it duplicates the row with the locale string (like fr-CA) and carries a parentId or some such to link that row back to the one in the default locale. The default site locale in DNN cannot be changed after the fact, by the way, so not too much to worry about there.

But not all fields make sense to localize, so it might make sense to know which fields are localizable and which are not, maybe that information can be in that supposed meta-data about those tables. For instance, for a Product, you want to localize the name and description but probably not the price and which categories it is part of. Preventing localization on the price would make it easier to change the price on all locales without worrying about the price becomes different per locale.

[X3Technology]

My initial thought on how to model this is for each content table to have a system-level "language" field and a self-referencing "master_id" foreign key.

So, you would end up with something like this:

SPORTS
Id | Language | Name | MasterId
1 | null | Soccer | 10 | null
2 | null | Ice hockey | null
3 | null | Basketball | null
4 | de | Fußball | 1
5 | de | Eishockey | 2
6 | de | Basketball | 3
7 | ja | サッカー | 1
8 | ja | アイスホッケー | 2
9 | ja | バスケットボール | 3

So:
'/api/content/sports/1' --> id specified, no language specified, so match by primary key --> return row 1
'/api/content/sports/1/lang/de' --> id and language specified, so match by language and masterId --> return row 4
'/api/content/sports/1/lang/es' --> id and language specified, so match language and masterId --> should we return a 404 or the default version?

[valadas]

Yep makes sense, any thoughs on meta-data about some fields that can't be localized, like a price or a quantity ?

Also, I would use the full locale name to support the scenario of something being different per location like fr-FR vs fr-CA

[X3Technology]

Also, I think we want to create an index on the Language + MasterId fields for query performance.

Also, probably should make localization an opt-in choice when creating the ContentType. No need to complicate the structure for people that don't want it. I would do this by having a button on the UX for the ContentType "Locallize this ContentType", which would then add the Language and MasterId columns to the table and also add the combo index to the table.

[X3Technology]

Yep makes sense, any thoughs on meta-data about some fields that can't be localized, like a price or a quantity ?

Also, I would use the full locale name to support the scenario of something being different per location like fr-FR vs fr-CA

Hadn't thought about non-localizable field types. That complicates things.
Agree on the full locale name.

[X3Technology]

For non-localizable fields (numbers, bit, etc), I am thinking the best way to handle this is to cascade row updates across all versions of the master record.

I would like to strive to maintain query performance which means avoiding unnecessary inner joins to return a large result set. But, performance is not such a big deal when doing a single row update, so if we have to iterate over all of the localized versions of a master record to update the non-localized fields, its not such a big deal.

I can't think of one off the top of my head, but there probably is a use case where someone could want to different values for a number or bit depending on the language. So, perhaps this needs to be an opt-in selection when adding a ContentField to the ContentType? Add a system level field called "is_localized" to the ContentField table. If the field is not localized, then an update to any version of the record will copy the field's value to all versions of the record. If the field is localized, then only update the version being touched.

[X3Technology]

Or, would it be simpler to just have a separate table for each language?

zz-sports-1-en-US
zz-sports-1-fr-FR
zz-sports-1-fr-CA

<prefix>-<contentType.name>-<portalid>-<lang>

It bloats the number of tables in the DB, but it's either that or bloat the number of rows in a single table and complicate the table structure. Maybe separate tables would be more straight-forward and easier to understand (and code).