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