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
