"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 <rja...@yahoo-inc.com> 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 >
