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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-16 : 20:38:32
|
MM writes "During developing a multi-lingual ASP application, I faced a tricky problem. I have a table containing names, let's say Cities. The names in the Cities table are needed to be stored in different langauges like English, French, Spanish, ...etc. The user can use any user interface language to list himself under a city. In the English version, he will see the English city names, in the Frensh version, Frensh city names. At the end, users can browse cities and the users listed under them.
During browsing, users have the option to view listed names in all languages, regardless of the user interface language they currently use.
So, when listing a city in English, I can view users under the English city name, Frensh city name, ...etc.
I have a some alternatives, but don't know if there's a robust mode for such data type that allow me to add other languages without the need to redesign the database? Here are some of the alternative I had:
1- All names are stored in the same Cities table as follows:
- ID: Auto number
- City Name: char
- ParentID: int
For a single city, names in different languages will be stored in multible records. For example, a record for English name, a record for Frensh name. The parent ID for the both of them will be the autonumber of the first inserted city name. Using this method, I can add a new language at any time without restructuring my design. The problem here is how to use the parent ID as a key and link it one-to-many to the user table key to list user under a city, and in the same time force database integrity, and using cascade delete triggers?
2- The second design was to use a table for the keys only:
Table 1
Field 1: CityID: Auto number
Field 2: Tempfield: char, used when inserting a new city because we can not insert an auto number.
Then another table (Table 2) contains city names with their language codes. The users table will be linked to Table 1 using its ID, and Table 2 will be used only for displaying city names in the user interface.
I need your advise.
Thank you."
|
|
|
|
|
|
|
|
Multi-Language Name Mirroring with Unique Key Required - How?