List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 1 2013 3:19pm
Subject:RE: Adding language support to tables
View as plain text  
You want to say either "Germany" or "Deutschland", depending on a "language_code"
somewhere?

Remove the strings you have in those tables now; add about 4 new tables, each one
paralleling the existing tables, but more rows and these columns:
* id (the PK of the existing table)
* language code (ENUM or TINYINT UNSIGNED indicating the language)
* spelling (utf8 version for the language in question)

There may be better ways to do your task, but see how this feels.  Sketch out the CREATE
TABLEs, INSERTs and SELECTs.

(Adding N columns for N languages is a maintenance and coding nightmare.  Tomorrow, you
will need N+1 languages.)

I would jettison the id in:
  `country_id` INT NOT NULL ,
  `country_code` CHAR(2) NOT NULL ,
and use country_code as the PRIMARY KEY, and make it ASCII, not UTF8.  That would turn the
4-byte id into a 2-byte string.

I gather you are using an new-enough NDB so that FOREIGN KEYs are implemented?

> -----Original Message-----
> From: Neil Tompkins [mailto:neil.tompkins@stripped]
> Sent: Wednesday, May 01, 2013 5:53 AM
> To: [MySQL]
> Subject: Adding language support to tables
> 
> Hi,
> 
> I've the following database structure of 4 tables for geographical
> information
> 
> 
> 
> CREATE  TABLE IF NOT EXISTS `mydb`.`country` (
> 
>   `country_id` INT NOT NULL ,
> 
>   `country_code` CHAR(2) NOT NULL ,
> 
>   `name` VARCHAR(255) NOT NULL ,
> 
>   PRIMARY KEY (`country_id`) ,
> 
>   UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) )
> 
> ENGINE = ndbcluster
> 
> DEFAULT CHARACTER SET = utf8
> 
> COLLATE = utf8_unicode_ci;
> 
> 
> 
> 
> 
> CREATE  TABLE IF NOT EXISTS `mydb`.`region` (
> 
>   `region_id` INT NOT NULL ,
> 
>   `name` VARCHAR(255) NOT NULL ,
> 
>   `country_code` CHAR(2) NOT NULL ,
> 
>   PRIMARY KEY (`region_id`) ,
> 
>   INDEX `FK_country_code` (`country_code` ASC) ,
> 
>   CONSTRAINT `FK_country_code`
> 
>     FOREIGN KEY (`country_code` )
> 
>     REFERENCES `mydb`.`country` (`country_code` )
> 
>     ON DELETE NO ACTION
> 
>     ON UPDATE NO ACTION)
> 
> ENGINE = ndbcluster
> 
> DEFAULT CHARACTER SET = utf8
> 
> COLLATE = utf8_unicode_ci;
> 
> 
> 
> 
> 
> CREATE  TABLE IF NOT EXISTS `mydb`.`city` (
> 
>   `city_id` INT NOT NULL ,
> 
>   `region_id` INT NOT NULL ,
> 
>   `name` VARCHAR(255) NOT NULL ,
> 
>   `latitude` DOUBLE NOT NULL ,
> 
>   `longitude` DOUBLE NOT NULL ,
> 
>   PRIMARY KEY (`city_id`) ,
> 
>   INDEX `FK_region_id` (`region_id` ASC) ,
> 
>   CONSTRAINT `FK_region_id`
> 
>     FOREIGN KEY (`region_id` )
> 
>     REFERENCES `mydb`.`region` (`region_id` )
> 
>     ON DELETE NO ACTION
> 
>     ON UPDATE NO ACTION)
> 
> ENGINE = ndbcluster
> 
> DEFAULT CHARACTER SET = utf8
> 
> COLLATE = utf8_unicode_ci;
> 
> 
> 
> 
> 
> CREATE  TABLE IF NOT EXISTS `mydb`.`district` (
> 
>   `district_id` INT NOT NULL ,
> 
>   `city_id` INT NOT NULL ,
> 
>   `name` VARCHAR(255) NOT NULL ,
> 
>   `latitude` DOUBLE NOT NULL ,
> 
>   `longitude` DOUBLE NOT NULL ,
> 
>   PRIMARY KEY (`district_id`) ,
> 
>   INDEX `FK_city_id` (`city_id` ASC) ,
> 
>   CONSTRAINT `FK_city_id`
> 
>     FOREIGN KEY (`city_id` )
> 
>     REFERENCES `mydb`.`city` (`city_id` )
> 
>     ON DELETE NO ACTION
> 
>     ON UPDATE NO ACTION)
> 
> ENGINE = ndbcluster
> 
> DEFAULT CHARACTER SET = utf8
> 
> COLLATE = utf8_unicode_ci;
> 
> 
> 
> 
> Basically I'm wanting to add language support for each table to
> translate the name field in each instance.  All other information will
> remain the same.
> 
> Therefore is my best approach to add some sort of look-up table with
> the translation...?
> 
> Thanks
> Neil
Thread
Adding language support to tablesNeil Tompkins1 May
  • RE: Adding language support to tablesRick James1 May
    • Re: Adding language support to tablesNeil Tompkins1 May