Re: database design question

"danjourno" <danjourno_at_hotmail.com> writes:

> 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
>

I think your existing model is better than the new one you propose for two reasons -

1. Your existing model is more within keeping to the relational model than your new proposal. The columns of a table should represent attributes of the entity. Your proposed new table structure appears to be taking attribute values and turning them into attribute names and that doesn't feel right to me.
2. From a practical point of view, I think adding and deleting rows is a bad idea. Depending on the database, this can be a very expensive operation - I have even seen databases where you could only delete the last column in a table. While I'd expect this would be only something you do rarely, I'd still avoid it. There is also the added complication on how changes in your table structure will affect your SQL commands. For example, if you add a new language or remove one, will you need to update all the SQL commands which insert/update/select from the table? With your existing model, this would not be necessary.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!