Multilingual Table

[Denis]

I wish to create a table in which field contents may be in any of the
following languages: English, Greek and Chinese.

Which architecture/structure do you suggest I use?

Should I perhaps split this into 3 tables each one with different
collations? Or would it be best to set the table to unicode? Or is
there a better option you think?

[Roy Harvey]

On 20 Jan 2007 12:06:16 -0800, "Denis" <k9******@hotmail.comwrote:

>I wish to create a table in which field contents may be in any of the
following languages: English, Greek and Chinese.

Which architecture/structure do you suggest I use?

Should I perhaps split this into 3 tables each one with different
collations? Or would it be best to set the table to unicode? Or is
there a better option you think?

Sounds like a perfect situation for UNICODE, a two-byte character set
that works with virtually any and everylanguage. Instead of char and
varchar, use Nchar and Nvarchar.

Roy Harvey
Beacon Falls, CT

[Denis]

Thank you for your reply.

That would mean though that the database would be double the size.
Right? And of course the nvarchat field can have up to 4000
characterts. Correct?

Finally, are you sure that both Greek and Chinese are supported in
Unicode? I checked and Greek seems ok, but Chinese...I am not so
sure. Does anyone know?

[Ed Murphy]

Denis wrote:

Finally, are you sure that both Greek and Chinese are supported in
Unicode? I checked and Greek seems ok, but Chinese...I am not so
sure. Does anyone know?

http://en.wikipedia.org/wiki/Unicode indicates that Chinese is
supported, though there's some debate over the details.

[Roy Harvey]

On 20 Jan 2007 17:10:21 -0800, "Denis" <k9******@hotmail.comwrote:

>Thank you for your reply.

That would mean though that the database would be double the size.
Right? And of course the nvarchat field can have up to 4000
characterts. Correct?

The char and varchar columns would double in size, yes. If you have
three such diverse languages to support that seems (to me) to be a
small price to pay.

Yes, the maximum column width would be reduced to 4000 characters. If
you are using SQL Server 2005 you have the alternative of
NVARCHAR(max).

>Finally, are you sure that both Greek and Chinese are supported in
Unicode? I checked and Greek seems ok, but Chinese...I am not so
sure. Does anyone know?

I am confident that Chinese support in UNICODE is at least as good as
Chinese support in any single-byte code page / collation.

Roy Harvey
Beacon Falls, CT

[Denis]

Actually I just read somewhere that you an actually define different
collations for different columns. Have you heard anything about that?
If that is true then I could simply stick to a default collation and
only put the chinese or greek collation on some of the columns only.
what do you think?

[Roy Harvey]

Yes, you can control the collation at the column level. It is all in
the Books On Line. That requires either three columns instead of
one, or three tables instead of one, with the corresponding changes to
the application to work that way, but it certainly will work. Unicode
nchar and nvarchar are also at the column level, and perhaps would
allow the application to be a bit simpler as there would only be one
version of each column in one version of the table.

Good luck!

Roy

On 22 Jan 2007 04:59:47 -0800, "Denis" <k9******@hotmail.comwrote:

>Actually I just read somewhere that you an actually define different
collations for different columns. Have you heard anything about that?
If that is true then I could simply stick to a default collation and
only put the chinese or greek collation on some of the columns only.
what do you think?

[Denis]

Yes, you can control the collation at the column level. It is all in
the Books On Line. That requires either three columns instead of
one, or three tables instead of one, with the corresponding changes to
the application to work that way, but it certainly will work. Unicode
nchar and nvarchar are also at the column level, and perhaps would
allow the application to be a bit simpler as there would only be one
version of each column in one version of the table.

thank you for your prompt reply.

What do you mean it "requires either three columns" ? I didn;t come
across any such info. What do you mean exactly?

what I want to do is the following:
TABLE A
---------------

Col1 (English) , Col2(Greek) , Col3 (Chinese)

where....
English: Default Collation
Greek: GREEK Collation
English: CHINESE Collation (or Unicode)
Since I can define collation for the 3 different columns, The question
is: Do i define the chinese/greek columns as UNICODE, or as Chinese
& Greek respectively? I was thinking the later, because the data
will take up less space and I will also have more room in the fields.
What do you think?

[Roy Harvey]

On 22 Jan 2007 06:53:36 -0800, "Denis" <k9******@hotmail.comwrote:

>What do you mean it "requires either three columns" ? I didn;t come
across any such info. What do you mean exactly?

If you want three different languages, without using UNICODE they have
to go into different columns or tables. Three languages, three
columns.

>what I want to do is the following:
TABLE A
---------------

Col1 (English) , Col2(Greek) , Col3 (Chinese)

Three columns.

>where....
English: Default Collation
Greek: GREEK Collation
English: CHINESE Collation (or Unicode)

I assume that last line was supposed to say Chinese: CHINESE.

>Since I can define collation for the 3 different columns, The question
is: Do i define the chinese/greek columns as UNICODE, or as Chinese
& Greek respectively? I was thinking the later, because the data
will take up less space and I will also have more room in the fields.
What do you think?

If you need English AND Greek AND Chinese, all at the same time, you
might as well use the language specific collations if they have what
you need. If you only need one language on any single row, just using
one column with Unicode will cover all three languages along with
countless others.

One other reason for using Unicode would be that if a fourth (or fifth
or sixth) language becomes necessary there are no database changes.

Roy Harvey
Beacon Falls, CT