Designing a database for a multilingual software

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)

Designing a database for a multilingual software

Author

Topic

orange
Starting Member

5 Posts

Posted - 2005-01-25 : 09:38:57

Hello!

I'm making a program that needs support for many languages. All data is stored in an MSSQL-server.

For instance, I need to store a place (the place's name is the same on all languages!) which has a description in different languages. With the current solution I was forced to create one "placeType" table which does nothing at all, and one placeTypeText table which contains the descriptions in different languages.

I hope you understand my problem. ATM, there's no way to add an FK from place to placeTypeText -- which is the wanted solution (or if anyone has a better solution!). Please, all advices are welcome!

Thanks!

Kristen
Test

22859 Posts

Posted - 2005-01-26 : 14:21:33

We use a mapping table.

The "Place" has a "GroupID" of the Group of "Descriptive Texts" that describes the Place. For us these are language translations, and also the way that different "Brands" choose to describe the Place.

For example, for a Product this may be the Description of the Product in French and English. But also the Brands "Wholesale" and "Retail" will have different Descritpions for the same Product - and each of those will have a French and English translation.

We have a Mapping table that says:

For Wholesale French use:
1. Wholesale French specific text if you have one
2. Use Generic French text
3. Use Wholesale English
4. Use Generic English text

Now, obviously, our intention is NOT to show any English text on the French version, but if we have Error messages, or perhaps new products, that have not yet been translated to French at least the English will show.

So in our "Text descriptions" table we have a "Brand" and a "Language" column.

The mapping table has "From Brand" and "From Language" and "Target Attribute" and "Target Language".

("Attribute" is used so that we can have Attibutes, like "Generic", that do not map specifically/directly to a Brand).

Hope my explanation isn't too complicated, and that it gives you some ideas to solving your problem.

As your diagram is right now you don't need "placeType", you just need a variable that indicates what Language should be given to the current user. So that is outside of the structure of your database - i.e. a Parameter to your queries


SELECT *
FROM  place P
      JOIN placeTypeText PTT
          ON PTT.placeTypeID = P.placeTypeID
         AND PTT.languageID = @MyLanguageIDParameter
WHERE P.objectID = @MyPlaceParameter

Kristen

orange
Starting Member

5 Posts

Posted - 2005-01-27 : 08:43:27

Thanks for your input Kristen!

I didn't quite understand all of your solution I think. But is this about right?

I found an intresting article which presents a "good design" of a multilingual website -- it actually is very similar with my design. (Note the Category table which has exactly the same functionality as my placeType.)
http://www.openmymind.net/localization/index2.html#databasedesign]

I don't understand how I'm able to remove the placeType from my design w/o getting errors from MSSQL about a missing LanguageID. The best solution would be to specify the LanguageID as an "outsider" like you said, and make MSSQL ignore that it can't connect it to another table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources