multilanguage table structure

gugubanana · #1 (**permalink**) 08-31-08, 12:32

elloo...

i have a product table which contains name and description field of each product. i like to have the name and description in four languages. can some one tell me what is the best practice to do this? any pointers to tutorials are of a great help.

many thanks.

currently my table stands at this:

create table product (
product_id int not null auto_increment primary key,
name varchar(50) not null,
description varchar(255) not null,
price decimal(10, 2) not null,
image_file_1 varchar(50),
image_file_2 varchar(50),
on_catalog_promotion tinyint(1) not null,
on_department_promotion tinyint(1) not null,
);

Pat Phelan · #2 (**permalink**) 08-31-08, 14:14

I have done similar things many times, internationalization is a bit of an art unless you follow a rigorous design philosophy.

My suggestion is to have everything that the application needs to internationalize stored as a "resource" instead of an attribute within tables. Decide on a base or default language up front, this avoids more problems than you can imagine. Always start with the base language, and have a "resource descriptor" with the base language description for EVERYTHING in your application.

Assuming you intend to use English as your base language, then your resource table should look something like this:

Code:

CREATE TABLE resource (
   resourceId			INT			NOT NULL
,  resourceLanguage		INT			NOT NULL
   PRIMARY KEY (resourceId, resourceLanguage)
,  description			NVARCHAR(99)	NOT NULL
   )
   
INSERT INTO resource (
   resourceId, resourceLanguage, description
   ) VALUES (0, 0, 'English')

This allows you to refer to all of your resources by their resourceId value in other tables. You can have descriptions in multiple languages (for example you might have 0, N for the English name for each language you support, and 1, N for the description of the language in its own localization. While the database doesn't care if you have a resource entry for N, 0 your application becomes many times easier to write and manage if you insist on this because it gives you a "default" untranslated value that your application can use whenever a localized description isn't available.

Instead of storing the name and the description in your product table, you'd have a resourceId for each of them, and do a join to retrieve the localized resource values. I always join against both the current localization as well as the default localization and Coalesce() the descriptions so that my application doesn't need to worry about whether a localized value is available for a given resource.

-PatP

gugubanana · #3 (**permalink**) 09-01-08, 10:03

thanks for replying back to me.

just to be sure that i understand what you have said, i've thought to recreate the table:

Code:

create table products(
 product_id int not null auto_increment primary key,
 price_sterling decimal(10, 2) not null,
 price_euro decimal(10, 2),
 price_usd decimal(10, 2),
 price_yen decimal(10, 2),
 on_catalog_promotion tinyint(1) not null,
 on_department_promotion tinyint(1) not null,
);

create table products_resource(
 resource_id int not null,
 resource_language int not null,
 name varchar(50) not null,
 description varchar(255) not null
);

insert into products(price_sterling, price_euro, price_usd, price_yen, on_catalog_promotion, on_department_promotion) values(10.12, 12.12, 24.24, 242.40);

insert into products_resource(resource_id, resource_language, name, description) value (last_insert_id(), 0, 'blahblahblah', 'more blahblahblah');

insert into products_resource(resource_id, resource_language, name, description) value (last_insert_id(), 1, 'blahblahblah', 'even more blahblahblah');

is the above what you mean? and if it is then is there is another way to insert last id instead of calling insert_last_id() function.

many thanks.

Pat Phelan · #4 (**permalink**) 09-01-08, 11:38

What I recommend is building the resources in just the default language at first, so that there are inserts always using a language of zero. This gets the basic information into the resource table quickly, cleanly, and with little or no fuss.

Then you need to build a translation page or application that allows the translator to enter translations for a one specific language. Look for resourceId values with a zero row but no corresponding row for the current language. Show the description from the zero row, and allow the translator to enter the description for the current language.

I'd also recommend creating a translation administration page to show descriptions in language zero that are missing descriptions in other languages. This greatly simplifies the "policing" effort to keep the resource table complete.

-PatP