まだまだ 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;