Any good resources, guides, approaches or tips to building custom modules with multi-lingual data? I don't mean localizing static text, but dynamic content. For instance, say I have a table called tblProducts with this data:

ProductID
ModuleID
ProductFamilyID (fk)
ProductTitle
ProductDescription
IsVisible
DateCreated
LastUpdated

I would want to store localized versions of the title and description. I could have up to 10 languages, so I want to design this smartly. My initial thought is to break out the title & description fields into a separate translation table with the ID and the local as keys.

tblProductTranslation

ProductID
Locale
ProductTitle
ProductDescription

When querying, I would always pass in the locale, join the tables etc etc.

Is this the general approach that most others use? It makes sense in my head, but if there are other examples or some DNN API stuff I should take advantage of, please comment. 

Thanks!

 

Erik,
there are generally multiple approaches, depending on the concrete situation. If you are having to localize a table like yours, you may
a) either separate the localizable properties into a dependent table using parent key + locale and use an inner join and filter by locale.
benefit: there is a guarantee that there might be a single translation only, but there is no guarantee, it exists
b) if there is a significant preference for one language and this is always populated, you may keep the columns in master table and use child table for additional languages only
Benefit: there is always at least one language available, but there might be a value for same language in base and translation table. You would need to use (slower) left JOIN.
c) if there is not always same initial language you may store initial language in base table as well. Benefit nearly same as b), a little bit more flexible but difficult to retrieve.
d) same base table as b) or c) and a central translation table with key (keyObject, keyID, keyproperty, language) key. you just need a single central table, but it is difficult to implement integrity, as it is not supported by SQL.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN