localize database

I am working on a project which I have to write a help module and it has to be l10n (coz we have Japanese and Chinese customers). And because I want the content to be editable TTW, everything has to be in the DB. Another requirement is that if the content hasn't been translated, the default content (in English will be displayed).

The following is the DB schema (mysql dialect)

CREATE TABLE `help_manager`.`helps` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic` varchar(512) NOT NULL,
`content` varchar(16384) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `help_manager`.`helps_l10n` (
`id` INT NOT NULL ,
`locale_id` INT NOT NULL ,
`topic` VARCHAR(512) NOT NULL ,
`content` VARCHAR(16384) NULL ,
PRIMARY KEY (`id`, `locale_id`) ,
INDEX `helps_fk` (`id` ASC) ,
INDEX `locale_fk` (`locale_id` ASC) ,
CONSTRAINT `helps_fk`
FOREIGN KEY (`id` )
REFERENCES `help_manager`.`helps` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `locale_fk`
FOREIGN KEY (`locale_id` )
REFERENCES `help_manager`.`locale` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

CREATE TABLE IF NOT EXISTS `help_manager`.`locale` (
`id` INT NOT NULL AUTO_INCREMENT ,
`locale` VARCHAR(8) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB


So here is how you select contents from the table with a specific locale

SELECT COALESCE (hl.topic,h.topic) , COALESCE(hl.content, h.content) FROM helps h LEFT JOIN (helps_l10n hl, `locale` l) ON (h.id=hl.id AND l.id=hl.locale_id) AND l.locale = 'ja_JP'

Side note
mysql UTF8 encoding:
utf8_bin: compare strings by the binary value of each character in the string

utf8_general_ci: compare strings using general language rules and using case-insensitive comparisons

utf8_general_cs: compare strings using general language rules and using case-sensitive comparisons


for example:
Ä == A => True
Ü == U => True

Comments (0)

Post a Comment