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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
