Multilanguage database design approach with parent_id

Databases
1

Hi,
I’m working on a multi languages site (travel agency)
this is the present schema


CREATE TABLE IF NOT EXISTS `lang` (
    `id` INT UNSIGNED NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `region` char(2) DEFAULT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `place` (
    `id` INT UNSIGNED NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `file_name` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`),
    UNIQUE KEY `pname` (`pname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `translation_place` (
  `id` INT UNSIGNED NOT NULL auto_increment,
  `place_id` INT UNSIGNED NOT NULL,
  `lang_id` INT UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`),
  CONSTRAINT `place_fk_1` FOREIGN KEY (`place_id`) REFERENCES `ss_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `place_fk_2` FOREIGN KEY (`lang_id`) REFERENCES `ss_lang` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

with the simple query


SELECT p.name,p.slug,p.file_name,(..... so on) FROM `place` AS p INNER JOIN `translation_place` AS tp ON `p`.`id` = `tp`.`place_id` WHERE `lang`.`lang_id`='1' AND `p`.`place_id`='1'

now I have to work with nested (only two levels) place
so I figure out a table like


CREATE TABLE IF NOT EXISTS `place` (
    `id` INT UNSIGNED NOT NULL auto_increment,
    `parent_id` INT UNSIGNED DEFAULT NULL,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `file_name` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`),
    UNIQUE KEY `pname` (`pname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. Is it a good approach ?
  2. How can I get the translation for the places with a parent id ?

Bye.

2

  1. if you need parent places, with child places related to those parent places, then yes

  2. same way – link to translation_place table for the parent as well as the child

3

Do you mean like this ?


CREATE TABLE IF NOT EXISTS `translation_place` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `place_id` INT UNSIGNED NOT NULL,
  `place_parent_id` INT UNSIGNED DEFAULT NULL,
  `lang_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `body` TEXT NOT NULL,
  PRIMARY KEY  (`id`),
  CONSTRAINT `place_fk_1` FOREIGN KEY (`place_id`) REFERENCES `ss_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `place_fk_2` FOREIGN KEY (`lang_id`) REFERENCES `ss_lang` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

thanks for the reply.

Bye

4

no, not like that

as i understand it, the place table is where you store the information about each place, including its parent, whereas the translation_place table is only intended to translate each place name, so you would not repeat the parent column or any other structure information there

i know i said “link” to the translation table, sorry – i should have said “join” to the translation table, which is what i really meant