Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnDeere
Posting Yak Master
191 Posts |
 Posted - 2003-06-03 : 10:37:57
|
| I need to design/modify our website database to handlemultiple languages. If a person chooses Spanish then I need to return all the text and character data stored in the database in Spanish. I am working on a couple of ideas now. 1. For each table store the language specific data in a child table (one row for each language)2. Store all the language specifc data in single table using a key like source_table_name,source_table_pk, language to get the data. Still working out how to handle all the different source table columns.I was interested if anyone had any experience in implementing multiple language support in a database?ThanksLance Harra |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-03 : 10:51:01
|
| I did one of these last year.Basically every piece of "content" had a parent record, then we had a row for every language in the content data table (language id was an fk into the languages table).Damian |
 |
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2003-06-03 : 12:19:13
|
| ThanksI was/am leaning towards that type of solution. The other ideas I have tried have all run into problems.Any words of caution or wisdom with that type of design.Lance Harra |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-03 : 12:26:45
|
It worked pretty well. It was also made trickier that any piece of content had extensible elements / dynamic forms etc. So for every language you had a group of elements related to the master content record.The fun bit was the fact that it had a customizable workflow / approval / versioning system so that each change went through stages of approval before it could go live. While one version was live you could have a changed version in approval. Not all langauges needed to be changed for the approval process to kick off.Was kinda hairy  The only gotcha is make sure you pass the language filter to each record call, or you get n records with every call, but that is kinda self explainatory.It's live on a fairly busy website now, if you drop me an email I will send you the URL (rather not post in public).EDIT : OH Yeah, every table's ID was a GUID    DamianEdited by - merkin on 06/03/2003 12:28:12 |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2003-06-03 : 12:40:36
|
| What benefit did you the GUID give you?I was thinking the content PK and language_id would be the key in the key into content data.Lance Harra |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-03 : 12:43:51
|
| Replication was the main reason for Guids. Plus, the guy that had architected a bunch of the system before I got there liked them.Damian |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-03 : 13:39:16
|
| So you have a page with the same content, 1 per language?What's unicode for then?Brett8-) |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2003-06-03 : 15:50:58
|
| Here is an example of the type of data I need to store and convert to multiple languages.I have a table that tracks information about a persons living conditionsType of house,electricity,running water,sanitation system,cooking facilitiesetcEach of these conditions is controlled by a lookup or code table that defines the choices for the specific condition.If a person requests information in English I need the English descriptions, if a person requests the information in Spanish I need the same description translated to Spanish. I was planning on defining the fields with unicode datatypes.Lance Harra |
|
|
|
|
|
|
|
|
Database design for multiple languages