CREATE TABLE `mit-oki`.`hierarchy` (
  `hier_id` VARCHAR(32) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(100) NOT NULL,
  `allow_multiple_parents` TINYINT(1) UNSIGNED,
  `allow_recursion` TINYINT(1) UNSIGNED,
  `saved` TIMESTAMP,
  PRIMARY KEY(`hier_id`)
)
TYPE = InnoDB;

CREATE TABLE `mit-oki`.`node_type` (
  `authority` VARCHAR(100) NOT NULL,
  `domain` VARCHAR(100) NOT NULL,
  `keyword` VARCHAR(100) NOT NULL,
  `description` VARCHAR(100) NOT NULL,
  `saved` TIMESTAMP NOT NULL,
  PRIMARY KEY(`authority`, `domain`, `keyword`)
)
TYPE = InnoDB;

CREATE TABLE `mit-oki`.`nodes` (
  `node_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `hier_id` VARCHAR(32) NOT NULL,
  `user_node_id` VARCHAR(100) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(100) NOT NULL,
  `authority` VARCHAR(100) NOT NULL,
  `domain` VARCHAR(100) NOT NULL,
  `keyword` VARCHAR(100) NOT NULL,
  `saved` TIMESTAMP NOT NULL,
  PRIMARY KEY(`node_id`),
  UNIQUE `Index_4`(`hier_id`, `user_node_id`),
  FOREIGN KEY `FK_hier_id` (`hier_id`)
    REFERENCES `hierarchy` (`hier_id`),
  FOREIGN KEY `FK_nodetype1` (`authority`, `domain`, `keyword`)
    REFERENCES `node_type` (`authority`, `domain`, `keyword`)
)
TYPE = InnoDB;

CREATE TABLE `mit-oki`.`parents` (
  `ref_node_id` INTEGER UNSIGNED NOT NULL,
  `parent_node_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`ref_node_id`, `parent_node_id`),
  FOREIGN KEY `FK_parents_1` (`ref_node_id`)
    REFERENCES `nodes` (`node_id`),
  FOREIGN KEY `FK_parents_2` (`parent_node_id`)
    REFERENCES `nodes` (`node_id`)
)
TYPE = InnoDB;

CREATE TABLE `mit-oki`.`children` (
  `ref_node_id` INTEGER UNSIGNED NOT NULL,
  `child_node_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`ref_node_id`, `child_node_id`),
  FOREIGN KEY `FK_children_1` (`ref_node_id`)
    REFERENCES `nodes` (`node_id`),
  FOREIGN KEY `FK_children_2` (`child_node_id`)
    REFERENCES `nodes` (`node_id`)
)
TYPE = InnoDB;

CREATE TABLE `mit-oki`.`hier_types` (
  `hier_id` VARCHAR(32) NOT NULL,
  `authority` VARCHAR(100) NOT NULL,
  `domain` VARCHAR(100) NOT NULL,
  `keyword` VARCHAR(100) NOT NULL,
  PRIMARY KEY(`hier_id`, `authority`, `domain`, `keyword`),
  FOREIGN KEY `FK_hier_types_1` (`hier_id`),
    REFERENCES `hierarchy` (`hier_id`)
  FOREIGN KEY `FK_hier_types_2` (`authority`, `domain`, `keyword`)
    REFERENCES `node_type` (`authority`, `domain`, `keyword`)

)
TYPE = InnoDB;