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 |
|
ed644
Starting Member
2 Posts |
 Posted - 2008-08-06 : 10:09:39
|
| I'm a design newbie, and barely know 3NF, so please bare with me if I seem to be asking a question that should have an obvious answer...I've been tasked with redesigning a database to support multiple languages. Currently, it supports French and English. There are many tables that have columns that are just English and French versions of each other. For example, there is a "Countries" table, with "CountryNameEnglish" and "CountryNameFrench" columns. Other tables have this same pairing of English and French columns whenever something needs multilingual support. This smells like a bad design, although I can't formally describle why.My (naive) solution:Create a "CountryNames" table with an "ID" column, and a column for each language. Remove "CountryNameEnglish" and "CountryNameFrench" from the "Countries" table, and replace it with a foreign key "CountryID." This way, it is at least obvious which tables will need updating when a new language is added.I would be grateful for any direction! Links, comments. Thanks!Tim |
|
|
ed644
Starting Member
2 Posts |
Posted - 2008-08-06 : 13:02:24
|
| Here's my revised idea:Create a Languages table with a LanguageName column. New languages can easily be added by adding new rows to this table. My previous idea required new columns to be added to tables.Each table that has a column that might be in multiple languages gets a reference table. So, since the Countries table has a CountryName that needs a value for both French and English, a CountryNames table is also created. It will have columns CountryNameID, LanguageName and Value. So row entries in this table might read "001923" (CountryNameID), "English" (LanguageName), and "Canada" (Value). |
 |
|
|
donebyfriday
Starting Member
1 Post |
Posted - 2008-08-06 : 15:10:18
|
| No expert here but,.... never constrain expansion in a design to the addition of COLUMNS, Rows, yes; columns, no! That would be my first reply.If the instance is uniquely identified...And the Language needed is identified...Just have a table keyed by instance and language with a text field for the language specific text... becomes open-ended (for number of languages supported) and easy to filter. Additional fields might be useful to reflect language specific issues also.Carl |
|
|
|
|
|
|
|
|
adding multilingual support