How to model localized elements
I am currently developing an e-commerce solution. One of the main requirements is that the store supports localized product details. The same store should be able to support multiple languages through the user's choice of language and / or browser preferences.
I have two tables:
Product (id, sku, price, ...)
ItemDetails (item_id, language, name, ...)
For each item there will be several lines corresponding to the item, where the pair (item_id, language) will be unique.
I would like to model this like:
class Item
{
public string sku;
public double price;
public ItemDetails Details;
}
Based on the user session, I would like the returned objects to have a Details object corresponding to the user's selected language (from their session).
What are some approaches to represent this?
a source to share
I am working on a similar globalization where a lot of dynamic content is stored in the database. The approach I use for multilingual dynamic data is as follows:
create table Item
(
ItemID
,Sku
,Price
,Title -- invariant, fallback culture
)
create table Item_Culture
(
ItemID
,CultureCode -- ex: en-US, en-CA, fr-CA, en-UK, fr-FR, en, fr
,Title
)
select i.ItemID, i.Sku, i.Price, coalesce(ic.Title, i.Title) as Title
from Item i
left outer join Item_Culture ic
on ic.ItemID = i.ItemID
and ic.CultureCode = @CultureCode
Where @CultureCode is a parameter passed from your application, and the Culture table contains a list of your different languages / cultures for reference or to define fallback language dependencies.
You can deploy this to multiple fallback languages if needed. This is a bit overwhelming for the database compared to a single language implementation, but will take into account the dynamic nature of your data and that's just the cost of a multilingual dynamic site. Caching will be your friend if you have data that doesn't change too quickly to reduce the load on the added database.
a source to share
Think about how you retrieve lists of objects
- they are cached
- they are filtered / sorted / dumped
- they are searched
- other applications (reports, partners) refer to them.
The answer determines how much translation logic can be in the application and how much can be in the database.
The application I'm currently working on has lists of objects that are only accessed in the application. Only 2 properties are translated and I don't need to sort those properties. I load lists of objects and each object already has translations for all languages stored in the dictionary. It's nice to think that there is only one entry per key in my cache.
Thinks to get complicated if you need to search, filter and sort by translated properties. Then I would try to get the logic in the database.
a source to share