-- Project Name : 救急外傷
-- Date/Time    : 2011/11/30 17:12:12
-- Author       : tro
-- RDBMS Type   : MySQL
-- Application  : A5:SQL Mk-2

-- 救急外傷 診断名
drop table emergency_injury_diagnosis cascade;

create table emergency_injury_diagnosis (
  emergency_injury_diagnosis_id INT not null comment '診断名ID'
  , emergency_injury_diagnosis_category_id INT not null comment '診断名カテゴリID'
  , diagnosis_name VARCHAR(128) not null comment '診断名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint emergency_injury_diagnosis_PKC primary key (emergency_injury_diagnosis_id)
) comment '救急外傷 診断名' engine=innodb default charset=utf8;

-- 救急外傷 診断名カテゴリ
drop table emergency_injury_diagnosis_category cascade;

create table emergency_injury_diagnosis_category (
  emergency_injury_diagnosis_category_id INT not null comment '診断名カテゴリID'
  , category_name VARCHAR(128) not null comment 'カテゴリ名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint emergency_injury_diagnosis_category_PKC primary key (emergency_injury_diagnosis_category_id)
) comment '救急外傷 診断名カテゴリ' engine=innodb default charset=utf8;

-- 救急外傷 処方薬
drop table external_injury_medicine cascade;

create table external_injury_medicine (
  external_injury_medicine_id INT not null comment '処方薬ID'
  , external_injury_diagnosis_id INT not null comment '診断名ID'
  , medicine_name VARCHAR(128) not null comment '処方薬名'
  , serious_level_0 tinyint not null comment '重症度0フラグ(0 or 1)'
  , serious_level_1 tinyint not null comment '重症度1フラグ(0 or 1)'
  , serious_level_2 tinyint not null comment '重症度2フラグ(0 or 1)'
  , serious_level_3 tinyint not null comment '重症度3フラグ(0 or 1)'
  , serious_level_4 tinyint not null comment '重症度4フラグ(0 or 1)'
  , kg_dose VARCHAR(32) comment '分量(/kg/dose)'
  , kg_day VARCHAR(32) comment '日分量(/kg/day)'
  , fixed_dose VARCHAR(32) comment '固定量(fixed dose)'
  , max_dose VARCHAR(32) comment '最大量(max dose)'
  , order_num INT not null comment '表示順番'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_medicine_PKC primary key (external_injury_medicine_id)
) comment '救急外傷 処方薬' engine=innodb default charset=utf8;

-- 救急外傷 診断名
drop table external_injury_diagnosis cascade;

create table external_injury_diagnosis (
  external_injury_diagnosis_id INT not null comment '診断名ID'
  , external_injury_diagnosis_category_id INT not null comment '診断名カテゴリID'
  , diagnosis_name VARCHAR(128) not null comment '診断名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_diagnosis_PKC primary key (external_injury_diagnosis_id)
) comment '救急外傷 診断名' engine=innodb default charset=utf8;

-- 救急外傷 診断名カテゴリ
drop table external_injury_diagnosis_category cascade;

create table external_injury_diagnosis_category (
  external_injury_diagnosis_category_id INT not null comment '診断名カテゴリID'
  , category_name VARCHAR(128) not null comment 'カテゴリ名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_diagnosis_category_PKC primary key (external_injury_diagnosis_category_id)
) comment '救急外傷 診断名カテゴリ' engine=innodb default charset=utf8;

-- 全身状態2回答選択肢
drop table body_condition2_answer_selection cascade;

create table body_condition2_answer_selection (
  body_condition2_answer_selection_id INT not null comment '全身状態2回答選択肢ID'
  , body_condition2_question_id INT not null comment '全身状態2設問ID'
  , answer_selection_name VARCHAR(128) not null comment '全身状態2回答選択肢名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint body_condition2_answer_selection_PKC primary key (body_condition2_answer_selection_id)
) comment '全身状態2回答選択肢' engine=innodb default charset=utf8;

-- 全身状態2設問
drop table body_condition2_question cascade;

create table body_condition2_question (
  body_condition2_question_id INT not null comment '全身状態2設問ID'
  , question_name VARCHAR(128) not null comment '設問名'
  , select_type tinyint not null comment '選択タイプ (1:単一, 2:複数)'
  , order_num INT not null comment '表示順番'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint body_condition2_question_PKC primary key (body_condition2_question_id)
) comment '全身状態2設問' engine=innodb default charset=utf8;

-- 外傷重症度
drop table external_injury_level cascade;

create table external_injury_level (
  target_kind INT not null comment '対象種類(1:外傷, 2:全身状態2)'
  , question_id INT not null comment '設問ID'
  , answer_selection_id INT not null comment '回答選択肢ID'
  , serious_level_a INT comment '重症度レベルA(日齢0-30)'
  , serious_level_b INT comment '重症度レベルB(月齢1-3)'
  , serious_level_c INT comment '重症度レベルC(月齢4-8)'
  , serious_level_d INT comment '重症度レベルD(月齢9-11)'
  , serious_level_e INT comment '重症度レベルE(1-2歳)'
  , serious_level_f INT comment '重症度レベルF(3-5歳)'
  , serious_level_g INT comment '重症度レベルG(6-7歳)'
  , serious_level_h INT comment '重症度レベルH(8-12歳)'
  , serious_level_i INT comment '重症度レベルI(13歳以上)'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_level_PKC primary key (target_kind,question_id,answer_selection_id)
) comment '外傷重症度' engine=innodb default charset=utf8;

-- 外傷回答選択肢
drop table external_injury_answer_selection cascade;

create table external_injury_answer_selection (
  external_injury_answer_selection_id INT not null comment '外傷回答選択肢ID'
  , external_injury_question_id INT not null comment '外傷設問ID'
  , answer_selection_name VARCHAR(128) not null comment '外傷回答選択肢名'
  , order_num INT not null comment '表示順番'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_answer_selection_PKC primary key (external_injury_answer_selection_id)
) comment '外傷回答選択肢' engine=innodb default charset=utf8;

-- 外傷設問
drop table external_injury_question cascade;

create table external_injury_question (
  external_injury_question_id INT not null comment '外傷設問ID'
  , external_injury_type_id INT not null comment '外傷タイプID'
  , question_name VARCHAR(128) not null comment '外傷設問名'
  , select_type tinyint not null comment '選択タイプ (1:単一, 2:複数)'
  , order_num INT not null comment '表示順番'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_question_PKC primary key (external_injury_question_id)
) comment '外傷設問' engine=innodb default charset=utf8;

-- 外傷タイプ
drop table external_injury_type cascade;

create table external_injury_type (
  external_injury_type_id INT not null comment '外傷タイプID'
  , type_name VARCHAR(128) not null comment '外傷タイプ名'
  , node_id tinyint not null comment 'ノードID (1:外傷項目, 2:交通外傷項目)'
  , order_num INT not null comment '表示順番'
  , delete_flag INT default 0 not null comment '削除フラグ(0:未削除, 1:削除済み)'
  , constraint external_injury_type_PKC primary key (external_injury_type_id)
) comment '外傷タイプ' engine=innodb default charset=utf8;