Best Practices to design multi-language web site database.
Technologies employed here are ASP.NET MVC and SQL Server.
EDIT :
I'm looking for table structure and relationship design.
+2
A:
On the DB side, use unicode (nvarchar and nchar) for your text.
Have a locale lookup table (containing the locale info you need - i.e. decimal point format), and for each table that has localized data add a foreign key to it.
Edit: (following comments)
Best practice is to use resource files for localization.
Oded
2010-03-02 18:51:12
@Oded : .NET Resources files are pain to maintain, I prefer full database localisation approach, store all localized "resources" in database.
Yoann. B
2010-03-02 18:54:41
@Yoann. B you can create your custom database resource provider: http://msdn.microsoft.com/en-us/library/aa905797.aspx
Gregoire
2010-03-02 18:56:24
don't you mean: (nvarchar and `nchar`)
KM
2010-03-02 18:56:48
@Gregoire : Ok, but how should i design my database tables ? Maybe add a "Culture" column on each tables containing values like "en-US", "fr-FR" ?
Yoann. B
2010-03-02 18:58:58
@KM - good catch. Fixed.
Oded
2010-03-02 19:01:40
@Yoann - what makes you think that having them on the DB will be any less of a pain to maintain?
Oded
2010-03-02 19:02:28
@Oded : since i can maintain them directly by a web interface, like any other data. ResX files are very painful, and requires recompilation etc..
Yoann. B
2010-03-02 19:05:24
A:
You could use/write a custom resx provider and implement a database store for them underneath. I've used a variation of Rick Strahl's provider - check this link for more information: http://west-wind.net/Weblog/posts/695968.aspx and it worked quite well..
Aim Kai
2010-03-02 19:06:15
A:
What you are looking for is described in detail at http://www.west-wind.com/presentations/wwDbResourceProvider/. This shows you how to create a custom resource provider with Admin UI that stores the data in Sql.
Greg Roberts
2010-03-02 19:06:34