storing 2 languages in a table?

[Destramic]

I'm developing an arabic site...but also want to view it in English...I'm thinking of using windows translating service (depending on good it is).

 

Now I was thinking of having columns in my table like so...english title, arabic title, english description, arabic description

 

As it can charge for words to be translated...when translating arabic to english I can store the results in the column so they would no longer have to be translated again

 

My question is...is it good practice to have separate columns for different languages?...thank you...any input would be great

[davidannis]

I doubt I would do it that way, instead I would have a table with three columns: item, value, and language:

Item              Value                               Language
title              Greetings                          en
title              あいさつ                           jp
description A Page about greetings    en
description なにか                               jp

That allows you to just change the select by adding WHERE language='xx' and leave all other logic the same and allows you to easily add a third language.

[bsmither]

The strategy I have seen is this: in a documents table, it contains all the relevant facts about the document, while in a second table, there is a reference to the document ID, a column to identify the language, and a column for each necessary phrase.

 

Documents table:

doc_id: 1

.....

 

Translations table:

trans_id: 1

doc_id 1

lang: en-US

title: Green Water

-----------

trans_id: 2

doc_id: 1

lang: es-ES

title: Aqua Verde

-----------

trans_id: 3

doc_id: 1

lang: ar-AR

title: [symbols]

[Destramic]

basically im going to require 2 variations of the same item and its columns but in two different languages...ummm

[requinix]

I would recommend what bsmither said (which is only a little bit more than davidannis said). I have actually used that pattern myself for a site where I needed to store multiple translations of various strings.

 

You definitely shouldn't be using multiple columns. As a rule of thumb, adding more of something you already have, such as more languages, should not require modifying database structures.

[Destramic]

but that way im gonna have 2 entries of the same item one in Arabic and one in English...not really what I was after unless I somehow have a translation table possibly

[requinix]

Yeah. It may not be what you were originally thinking but it is the best solution. Remember that databases are specifically designed to deal with many rows of data. Shift those rows into columns and you lose one of the huge advantages of a relational database.

[Psycho]

I would propose that the bsmither proposed needs further denormalization. If you have common text in the header or footer of the pages, you would not want to define the same text for every page. I think there needs to be the ability to applyt he same text sections to multiple documents/pages. I would propose something such as this:

 

documents/pages (defines each unique page)

doc_id, doc_name

 

text (defines the different sections of text)

text_id, text_name

 

doc_text (table to associate which text fields appear on which pages)

doc_id, text_id

 

translations (defines the different language values for the sections of text)

text_id, language, value