Re: database design question

thank you for that..

you have answered alot of my questions regarding possible bad practices in implementing the second option.
the only problem with the first option is that mysql (btw I am using mysql) does not support different character sets within the same column. for example, if I have a label in english and another label in chinese for that same phrase, then I will be trying to put two labels of different character sets in the one collumn. Mysql does support having a diferent charset per column within a table with version 4 but not multiple charsets in the one column.

Maybe I should use a simmilar design to the first but add more tables for each new language?

thanks

dan

"Steve Kass" <skass_at_drew.edu> wrote in message news:auitvc$gdh$1_at_slb0.atl.mindspring.net...
> 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:58:20 CET