Data model for storing translated data

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data model for storing translated data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-10 : 09:16:16

Keith Bloom writes "Hi there,

I develop applications that present content on websites and many of the sites are in multiple languages. So I am storing my English text in the main table and using a bucket for the tranlsations which has a language and the foreign keys to the tables with English content.

This is simplified example of this in action:



-- Web polls -- 

Poll_question:
id (int) PK
question_en (varchar) 500

Poll_answer:
id (int) PK
answer_en (varchar) 500
poll_question_id

Poll_translations:
id (int) PK
Poll_question_id (int)
Poll_answer_id (int)
Language_id (int) 
Description (nvarchar) 1000

This works well as I can left join to this table and it is a nice palce to keep the data. The downside comes when a table has more than one field to translate.

My question is, do you have suggestions or methods for modelling this type of system?

Thanks, Keith."

Kristen
Test

22859 Posts

Posted - 2006-07-11 : 07:23:35

We use a single self-referencing table, thus all columns are available for the foreign language version.

We make the "language" column part of the PK

Where some of the columns have no relevance to the foreign language version we use two tables:

CommonStuff
and
LanguageSpecificStuff

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources