-- ------------------------------------------------
-- Org.lvl. data for UM
-- Created 20131114
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `org_lvl` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `system` VARCHAR(8) NULL ,
  `varbl` VARCHAR(40) NULL ,
  `vtext` VARCHAR(60) NULL ,
  `delete` TINYINT , 
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `sys_varbl` (`system` ASC, `varbl` ASC) );
  
CREATE TABLE IF NOT EXISTS `um_role_org_lvl` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `system` VARCHAR(8) NULL ,
  `role_id` INT NULL ,
  `org_lvl_id` INT NULL ,
  `low` VARCHAR(40) NULL ,
  `high` VARCHAR(40) NULL ,
  `um_delete` TINYINT ,
  PRIMARY KEY (`id`) ,
  INDEX `sys` (`system` ASC) ,
  INDEX `role_idx` (`role_id` ASC) ,
  INDEX `org_lvl_idx` (`org_lvl_id` ASC) ,
  UNIQUE INDEX `uni` (`role_id` ASC, `org_lvl_id` ASC, `low` ASC, `high` ASC) ,
  CONSTRAINT `role_ol_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `um_role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `role_ol_ol`
    FOREIGN KEY (`org_lvl_id` )
    REFERENCES `org_lvl` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
