Multilingual application and database design

Hi,

The application I am creating requires that its content is translated in various languages. Let's say for example we are talking about a web shop : the products' description and category must be displayed in English by default, and in French in the French section of the web site.

Usually, I design my database like this :

But is this the right way to design the DB with NetTiers ? It seems very complicated to manage my Entities object this way !

Shouldn't I design it another way, for easier manipulation of the entities (although I know this design is evil !) :

Item (= the item in the default language)
- ItemID
- CultureID
- Name
- Description

LocalizedItem (= the item in other languages)
- LocalizedItemID
- ItemID
- CultureID
- Name
- Description

Any help appreciated, thanks.

Hi Lex!

Although I am no dba, living in Montréal, I am also confronted to multilingual development. Your design does not look that evil to me. There is some overhead, but there is a cost to being multilingual. However, you could still use a simpler design depending on "how multilingual" you want your application to be. For example, I have been working on a bilingual web site, and it is unlikely that we will have to support other languages than French and English, so we decided to simply double all the text field. In your case, you would replace Name with Name_fr and Name_en, and you would do the same with Description.

Also, if you are never going to have to support multi-byte languages, you could simply use varchar instead of nvarchar, char instead of nchar, etc.

Cheers!

JF
 

Well in my application, almost everything that will be displayed in the front-end will be multilingual (and that is a lot of data, like hundreds thousands of records). The supported languages will be "dynamic" : we'll start with English, but soon add french, german, etc ... because it is for a worldwide (and growing !) company.

So we need to find the best compromise between performance and code maintenance.

Lex,

Your design is good for dynamic content.

As far as static content (asuming you're using asp.net 2.0) and the size of your application you may want to consider implementing your own ResourceProvider ... here are few references:

http://www.leftslipper.com/ShowFaq.aspx?FaqId=9
http://www.codeproject.com/aspnet/customsqlserverprovider.asp

Lex:

Well in my application, almost everything that will be displayed in the front-end will be multilingual (and that is a lot of data, like hundreds thousands of records). The supported languages will be "dynamic" : we'll start with English, but soon add french, german, etc ... because it is for a worldwide (and growing !) company.

So we need to find the best compromise between performance and code maintenance.

I have figured out that I could enhance my design:

View : ItemView
ItemID
LanguageID
Name
Description
etc ...

I guess, this would be much less painful to integrate this with nettiers than my first design for READ operations. But Views Entities don't implement the Create/Update/Delete on the underlying tables, so it makes it still a bit complicated ...

What do you nettiers gurus think about it ? :-)

Lex,

 If you're taking about doing your updates from web pages, you can always use the EntityDataSource which allows you to specify what methods are called for CRUD so you can handle them accordingly.

I do this where say I have a Product which has various related entities.  I pull the data I need together using a View, and display using the view, but on the CUD methods I handle them myself.

HTH

swin

Thanks for the tip.

My application has a WebForms AND a WinForms (for back-office) interface. So what would be the best way to work with WinForms ?

Hi,

  I think what Swin was hinting at, and I agree with, is that you create your own Custom Stored Procedures that 'compliment' the View that you have created.

The View is what is used to create the Entity - but as you point out, that does not generate the CRUD operations that would obtain automatically if using a Table... that is because a View 'reveals' less meta data than a Table. The solution is to create stored procedures to perform Insert, Update, Delete as required, and use a specific naming convention for these Custom Stored Procedures. The default would be _ViewOrTableName_CustomStoredProcedureName  ...  this is expressed as  _{0}_ in the Properties window of the NetTiers.cst.

During generation, .NetTiers templates will map the name of your Custom Stored Procedure to the matching Entity to be created by your View, and when you look at the methods exposed by DataRepository.YourViewNameProvider, you will see methods such as CustomStoredProcedureName.

Hope that answers your question, and that I have not patronised you - I try to post responses that people new to the framework will follow too 

Regards,

David

P.S I once contributed to a proof of concept for automatic transalation of technical documents - for the maintenance of Nuclear Power Stations!.... scary indeed!

Thanks for the clear explaination mcquiggd.

As I have to create about 30 multilingual entities from Views, it would be even better to be able to create these stored procedures automatically. From my understanding, it is not possible with CodeSmith to retrieve the underlying tables from a view, is that correct ? If not, then it's no big deal : I would have to work with naming conventions. Something like : ProductMultilingualView is the multilingual view of Product and ProductMultilingual.

Certainly Codesmith / NetTiers will automatically create some basic Find, Get, GetPaged and GetList for your Views - just not the CRUD methods it can create against a Table..... any updates to your View will of course have to be handled by Custom Stored Procedures, which is no real pain... 

One thing you can do is to create a Table that has the columns that correspond to those that would be returned from your View, generate against that, and amend the generated Stored Procedures to point to the relevant tables (they at least provide a good starting point, you just have to create some joins and potentially alias some columns) - as long as the paramaters and returned data match what .NetTiers is expecting to see, it seems to be happy to treat the Entity as if it was a Table, even though your Stored Procedures mask that fact that data is being returned and updated from different tables, and you might have deleted the table. I am using this method to perform cross-database queries that appear as a single Entity.

 Regards,

 David

Thanks for your help, I mark your post as answer. I am glad that you are very active on the forum !

I'll just need to develop the Save (or insert + update) and Delete stored procedures for my view, and it should work ;-)

Thanks again.

Hi Lex,

Which solution did you finally apply to your application & DB Design. I'm currently using the exact same design in my Database (except for some details, e.g. I don't have default description in the non localized table).

I have the following schema:

• TABLE_ITEM (ItemID, ItemCode, TimeStamp)
• TABLE_ITEM_LOCAL (ItemLocalID, ItemID, LanguageID, Label, TimeStamp)
  
• VIEWLOCAL_ITEM (ItemID, ItemLocalID, LanguageID, ItemCode, Label)
• TABLE_LANGUAGE (LanguageID, LanguageName, CultureCode, ...)
  

This way, I can query my View to get the Localized item depending on the Language ID. I built the view using Cross Join with the Language table so I always get a record for the asked language, even when no record does exist. Example:

SELECT     TOP 100 PERCENT ItemID, ItemCode, LanguageID, ItemLocalID, Label, TABLE_ITEM_LOCAL.[TimeStamp]
FROM         dbo.tbl_StatusType CROSS JOIN
                      TABLE_LANGUAGE LEFT OUTER JOIN
                      TABLE_ITEM_LOCAL ON ItemID = TABLE_ITEM_LOCAL.ItemID AND
                      TABLE_LANGUAGE.LanguageID = TABLE_ITEM_LOCAL.LanguageID
ORDER BY ItemID, TABLE_LANGUAGE.LanguageID

There are various problems with such solution:

- I don't have a default language translation (I could use a case with a subselect in my View, but I need to carry for performances as I have large tables with many records). A good solution would be to handle the translation and default language in the DataLayer (or ServiceLayer?)...

- Views do not provide DeepLoading or CRUD capabilities as Tables allow... That's really a pain.

Does anyone have an idea if adding support for such design into the template would be difficult ? if so, how could it be done (from a design perspective...).

Thanks. 

I just saw tyhe following post in Patches forum:

http://community.codesmithtools.com/forums/storage/32/23838/patch.txt

It seems great, but I don't get how to integrate it to the NetTiers templates...

Could we think to add this to the Default NetTiers templates and activate the features using a simple boolean CodeSmith Setting "EnableLocalizedContent={True/False}". 

Also, I would suggest to specify the Culture Table (which in most case will be a Table like tbl_language that contains LanguageID, Language Name and associated Culture Code...

Is someone interested in working on this to integrate it to the templates ? I have no experience in templates editing so I don't feel comfortable to modify them with such changes on my own (at least, doing it with someone else that already did it...). Furthermore, I think this can be a grat addition to the Templates (and still not bother other who don't need it as we would make it disabled by default).

Thanks ! 

Mike.