JTVT2019現在のデータベース構造

まだまだ refineせねばならないと思いますが、現段階での Database DDLを記録しておきます

 ###############################################################################################
 ### SQL for JTVT2019 
 ### Based on this DDL, the program is constructed.
 ### Programmed by Shigeru SAITO, MD, FACC, FSCAI, FJCC
 ### on March 4th, 2018.
 ### revised on April 12th, 2018
 ###
 ### DB Name : jtvt2019
 ###############################################################################################

CREATE TABLE IF NOT EXISTS `hp_tbls` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `hp_code` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `hp_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `zip_code` VARCHAR( 7 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `ken_name` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `hp_address` VARCHAR( 256 )CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `country_code` TINYINT( 2 ) NOT NULL DEFAULT '1',			/* Country Code; By using this code , 1: JAPAN*/
                              /* interface such as feet or lb can be aplied. >10: feet/lb */
  PRIMARY KEY (`id`),
  UNIQUE(`hp_code`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `dr_tbls` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `kanji_sirname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `kanji_firstname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',	
  `english_sirname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `english_firstname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',	
  `hp_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `hp_name`  VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `job_kind` TINYINT( 2 ) NOT NULL DEFAULT '1',
  `email` VARCHAR( 128 ) NOT NULL DEFAULT '',
  `dr_pwd` VARCHAR( 512 ) NOT NULL DEFAULT '',
  `clue` TINYINT( 1 ) NOT NULL DEFAULT '1',
  `hint` VARCHAR( 512 )NOT NULL DEFAULT '',
  `login_date` DATE NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ip` VARCHAR( 15 ) NOT NULL DEFAULT '000.000.000.000',
  `dr_url` VARCHAR( 60 ) NOT NULL DEFAULT '',
  `is_active` BOOLEAN NOT NULL DEFAULT '0',
  `is_usable` BOOLEAN NOT NULL DEFAULT '1',
  `is_deleted` BOOLEAN NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE(`email`),
  INDEX(`email`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `login_logs` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `dr_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `login_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `login_ip` VARCHAR( 15 ) NOT NULL DEFAULT '000.000.000.000',
  PRIMARY KEY(`id`),
  INDEX(`dr_tbl_id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `abstract_tbls` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `hp_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `dr_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `submission_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `abstract_topic1` tinyint( 2 ) NOT NULL default '0', /* TAVI, MitraClip, etc  */
  `abstract_topic2` tinyint( 2 ) NOT NULL default '0', /* Complications, etc  */
  `abstract_title` VARCHAR( 200 ) NOT NULL DEFAULT '',
  `abstract_content` VARCHAR( 2000 ) NOT NULL DEFAULT '',
  `is_selected` BOOLEAN NOT NULL DEFAULT '1',
  `is_deleted` BOOLEAN NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_access_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `time_slot_tbls` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `begin_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `end_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',	
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `dr_role_tbls` (
  `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `dr_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `time_slot_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `session_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
  `role_kind` tinyint( 2 ) NOT NULL default '0',
  `is_active` BOOLEAN NOT NULL DEFAULT '0',
  `is_usable` BOOLEAN NOT NULL DEFAULT '1',
  `is_deleted` BOOLEAN NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `session_tbls` (
    `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
  `session_typing_japanese` VARCHAR( 100 ) NOT NULL DEFAULT '',
  `session_typing_english` VARCHAR( 100 ) NOT NULL DEFAULT '',
    `session_sub_title_japanese` VARCHAR( 100 ) NOT NULL DEFAULT '',
  `session_sub_title_english` VARCHAR( 100 ) NOT NULL DEFAULT '',
  `session_begin_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `session_end_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',	
  `session_objective_japanese` VARCHAR( 300 ) NOT NULL DEFAULT '',
  `session_objective_english` VARCHAR( 300 ) NOT NULL DEFAULT '',
  `is_deleted` BOOLEAN NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;