groups and sub-group up to 5 level database structure

Question

i was wondering what is the best structure for a table in mysql, The structure needs to have:

Parent (level 0)
  -region (inside regions are)(level 1)
     -countrys (inside countrys are)(level 2)
       -Districts (level 3)

So I need to store all that info but in the same table, any clues?? If you need to know i building this app on cakephp.

Thanks in advance.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Barmar, Apr 05 '13 at 23:47
Because this is for permitions, like if the user1 is part of id '4' and id '4' is a country(level 2) then he is only able to see and do certain things only. — Yair Villar, Apr 05 '13 at 23:56
possible duplicate of [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) — Bill Karwin, Apr 06 '13 at 00:11

leonardo_assumpcao · Accepted Answer · 2013-04-06T03:22:10.733

I've impulsively made an EER diagram for your situation.
Please tell me if it works (and if you agree), I'll be awaiting your feedback.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `hierarchy` ;
CREATE SCHEMA IF NOT EXISTS `hierarchy` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `hierarchy` ;

-- -----------------------------------------------------
-- Table `hierarchy`.`level`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `hierarchy`.`level` (
  `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `hierarchy`.`location`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `hierarchy`.`location` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `parent` INT UNSIGNED NULL ,
  `name` VARCHAR(50) NOT NULL ,
  `level` TINYINT UNSIGNED NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_location_location_idx` (`parent` ASC) ,
  INDEX `fk_location_level1_idx` (`level` ASC) ,
  INDEX `index_name` (`name` ASC) ,
  CONSTRAINT `fk_location_location`
    FOREIGN KEY (`parent` )
    REFERENCES `hierarchy`.`location` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_location_level1`
    FOREIGN KEY (`level` )
    REFERENCES `hierarchy`.`level` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `hierarchy` ;

-- -----------------------------------------------------
-- Placeholder table for view `hierarchy`.`details_location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hierarchy`.`details_location` (`location_id` INT, `location_name` INT, `parent_id` INT, `level_id` INT, `level_name` INT, `children` INT);

-- -----------------------------------------------------
-- View `hierarchy`.`details_location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `hierarchy`.`details_location`;
USE `hierarchy`;
CREATE  OR REPLACE VIEW `hierarchy`.`details_location` AS
SELECT
  x.`id` AS `location_id`,
  x.`name` AS `location_name`,
  IFNULL(x.`parent`, 0) AS `parent_id`,
  y.`id` AS `level_id`,
  y.`name` AS `level_name`,
  (SELECT COUNT(*) FROM location AS l WHERE l.parent = x.id) AS `children`
FROM location AS `x`
INNER JOIN `level` AS `y`
  ON (x.`level` = y.id);
USE `hierarchy`;

DELIMITER $$
USE `hierarchy`$$

CREATE TRIGGER `insert_location` BEFORE INSERT ON location
FOR EACH ROW BEGIN
  DECLARE x INT UNSIGNED DEFAULT 1;

  IF NEW.parent IS NOT NULL THEN
    SELECT `level`+1 INTO x FROM location WHERE id = NEW.parent;
  END IF;

  SET NEW.`level` = x;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `hierarchy`.`level`
-- -----------------------------------------------------
START TRANSACTION;
USE `hierarchy`;
INSERT INTO `hierarchy`.`level` (`id`, `name`) VALUES (1, 'parent');
INSERT INTO `hierarchy`.`level` (`id`, `name`) VALUES (2, 'region');
INSERT INTO `hierarchy`.`level` (`id`, `name`) VALUES (3, 'country');
INSERT INTO `hierarchy`.`level` (`id`, `name`) VALUES (4, 'district');

COMMIT;

INSERT INTO location (parent,name,level) VALUES
(NULL,'a1',NULL), (NULL,'a2',NULL), (NULL,'a3',NULL);

INSERT INTO location (parent,name,level) VALUES
(1,'b1',NULL), (1,'b2',NULL), (2,'b3',NULL),
(2,'b4',NULL), (3,'b5',NULL), (3,'b6',NULL);

INSERT INTO location (parent,name,level) VALUES
(4,'c1',NULL), (4,'c2',NULL), (5,'c3',NULL),
(5,'c4',NULL), (6,'c5',NULL), (6,'c6',NULL),
(7,'c7',NULL), (7,'c8',NULL), (8,'c9',NULL),
(8,'c10',NULL), (9,'c11',NULL), (9,'c12',NULL);

SELECT * FROM details_location;

[(Mostly) auto-generated code from MySQL Workbench 5.2]
Note that the trigger insert_location determines item's level at registration time, increasing so the parent's level by one unit.

groups and sub-group up to 5 level database structure

1 Answers1