Posted by Erland Sommarskog on 02/20/07 23:09

(jackal_on_work@yahoo.com) writes:
> I have a database and want to store data in Spanish and English. To
> accompish this:
>
> 1. Do i need to create separate tables for both the languages like
> items_en and items_sp?

It's impossible to answer without more knowledge about your requirements
and what sort of data we are talking about. But seprate tables does not
sound like a good idea.

One upon a time, our tables looked like this:

CREATE TABLE entities (entityid int NOT NULL,
entityname varchar(30) NOT NULL,
entitynamefor varchar(30) NOT NULL,
...

Where "name" was the Swedish name, and "namefor" the English name. But
when we entered the Finnish market, that was not acceptable, so we
switched to:

CREATE TABLE entities (entityid int NOT NULL,
entityname varchar(30) NOT NULL,
...

CREATE TABLE entitynames(entityid int NOT NULL,
languageid smallint NOT NULL,
entityname varchar(30) NOT NULL,
PRIMARY KEY (entityid, languageid))

That is, for every entity we have a look-up table for, there is also
a name table, that holds the name for the various languages. The
main table also has the name in the home language of the system, since
we cannot ensure that the users enters name in all languages that the
system support.

But I have to idea whether this is applicable to your system.

> 2. If I opt for the UTF16 charset what single collation setting can I
> use?

If all you need to support is English and Spanish, you can go with
varchar if you like.

If you need sorting of your data, our scheme with a subtable of the
names is not workable. You can set the collation per column, so you
can have one Spanish column with Modern_Spanish_CI_AS and one
English column with Latin1_General_CI_AS.

In practice, though, it should work just fine to have Modern_Spanish
everywhere, since the effect on English will be miniscule. It's different
if you want to use Traditional_Spanish, where CH and LL sorts as
separate letters; that is not suitable for English.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]