List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:May 1 2013 3:36pm
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?"

*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 <rjames@stripped> 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.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