Re: database design question

Dan,

  The first way is the only way to go, in my opinion. If you use the second option, you are no longer storing the label's language in the table; you are storing it instead in the name of the table's column. Data goes in tables; data does not go in column names. That's a pretty fundamental principle of database design, and for good reason. A row should represent a single entity, and the column values should represent attributes of that entity. "oui" is not an attribute of "yes", however "oui" is an attribute of "yes in French", the "how it is written" attribute.

  You should also never have to add columns to a table except when you come to realize that you must keep track of a new attribute not originally in the design. A new language is not a new attribute - you should only need to add a new column if you do something like expand this to a voice-interactive system and decide to store .wav files for each label so that it can be pronounced, not printed. The .wav file is the "how it sounds" attribute of "yes in French", and would deserve a new column. As you have it now, what would you do if you needed the .wav files? Double the number of columns? Add an entirely new table to store something about "Label" that you already have a table for?

  I'm not trying to put you down - your question is common, and the second structure you suggest is a nice one for people to look at, but it's not a good table design. Save the second format for human-readable output, to be produced by a report-writing package, and stick with the first design. It will provide many benefits - one for example would be if there is a global constant for a client's language, say _at__at_lang. Then you have the same query for every language:

  select label from labels
  where index = 'yes'
  and language_id= _at__at_lang

Otherwise you need to write all new queries for every language.

Steve Kass
Drew University

danjourno wrote:

>i am having a problem trying to figure out which way to go with adding
>multilingual support to my database.
>
>I am trying to store labels for multiple languages in my DB so I am creating
>a table called labels
>currently I have a table with three columns. One for an index, one for the
>actual label, and one that links to a language ID.
>all index values are in english
>
>index | label | language_id
>---------------------------------------
>yes yes EN
>yes oui FR
>
>primary key(index, language_id)
>
>
>
>I am thinking that I should actually change this to add more collumns rather
>that rows.
>like below.
>
>index | label_eng | label_fr | label_ch |
>label_etc.......
>-----------------------------------------------------------------------
>yes yes oui ????
>.......
>
>and then continue to add more columns as I add more languages.
>
>==================
>
>my question is..: is this second option a better option? as far as database
>design goes, taking into account that the size of these label fields may
>come to over 150 chars..?
>
>BTW I am currently using 3.23 but I understand that version 4 supports
>multiple charsets.
>this is one of my reasons for thinking of this change
>
>thanks heaps
>
>dan
>
>
>
>
>
>
Received on Sat Dec 28 2002 - 02:17:17 CET