"You want to say either "Germany" or "Deutschland", depending on a
"language_code" somewhere?"
*Yes, this is the exact issue I'm on on about. *
I like your idea. Does anyone else have any thoughts before I try this way
?
On Wed, May 1, 2013 at 4:19 PM, Rick James wrote:
> 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.tompk...@googlemail.com]
> > 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
>