273 lines
7.1 KiB
MySQL
273 lines
7.1 KiB
MySQL
|
CREATE TABLE `coding_sys` (
|
||
|
|
`coding_sys_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`abb` varchar(10) UNIQUE,
|
||
|
|
`name` varchar(255),
|
||
|
|
`description` text,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `races` (
|
||
|
|
`race_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`site_id` integer,
|
||
|
|
`coding_sys_id` integer,
|
||
|
|
`name` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `religions` (
|
||
|
|
`religion_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`site_id` integer,
|
||
|
|
`coding_sys_id` integer,
|
||
|
|
`name` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `ethnics` (
|
||
|
|
`ethnic_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`site_id` integer,
|
||
|
|
`coding_sys_id` integer,
|
||
|
|
`name` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `countries` (
|
||
|
|
`country_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`site_id` integer,
|
||
|
|
`coding_sys_id` integer,
|
||
|
|
`name` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `patients` (
|
||
|
|
`pat_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pat_num` varchar(255) UNIQUE,
|
||
|
|
`pat_altnum` varchar(255) UNIQUE,
|
||
|
|
`prefix` varchar(255),
|
||
|
|
`name_first` varchar(255),
|
||
|
|
`name_middle` varchar(255),
|
||
|
|
`name_maiden` varchar(255),
|
||
|
|
`name_last` varchar(255),
|
||
|
|
`suffix` varchar(255),
|
||
|
|
`name_alias` varchar(255),
|
||
|
|
`gender` varchar(255),
|
||
|
|
`birth_place` varchar(255),
|
||
|
|
`birth_date` date,
|
||
|
|
`address_1` varchar(255),
|
||
|
|
`address_2` varchar(255),
|
||
|
|
`address_3` varchar(255),
|
||
|
|
`city` varchar(255),
|
||
|
|
`province` varchar(255),
|
||
|
|
`zip` varchar(255),
|
||
|
|
`email_1` varchar(255),
|
||
|
|
`email_2` varchar(255),
|
||
|
|
`phone` varchar(255),
|
||
|
|
`mobile_phone` varchar(255),
|
||
|
|
`mother` varchar(255),
|
||
|
|
`account_number` varchar(255),
|
||
|
|
`marital_status` varchar(255),
|
||
|
|
`country_id` integer,
|
||
|
|
`race_id` integer,
|
||
|
|
`religion_id` integer,
|
||
|
|
`ethnic_id` integer,
|
||
|
|
`citizenship` varchar(255),
|
||
|
|
`death` bit,
|
||
|
|
`death_date` datetime,
|
||
|
|
`link_to` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pat_comments` (
|
||
|
|
`pat_com_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pat_id` integer,
|
||
|
|
`comment_text` text,
|
||
|
|
`user_id` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pat_identities` (
|
||
|
|
`pat_idt_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pat_id` integer,
|
||
|
|
`identity_type` varchar(255),
|
||
|
|
`identity_num` varchar(255),
|
||
|
|
`effective_date` datetime,
|
||
|
|
`expiration_date` datetime,
|
||
|
|
`create_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pat_diagnose` (
|
||
|
|
`pat_dia_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pat_id` integer,
|
||
|
|
`diag_code` varchar(255),
|
||
|
|
`diag_comment` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pat_visits` (
|
||
|
|
`pv_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pv_num` varchar(255) UNIQUE,
|
||
|
|
`pat_id` integer,
|
||
|
|
`episode_number` integer,
|
||
|
|
`pv_class_id` integer,
|
||
|
|
`bill_account` varchar(255),
|
||
|
|
`bill_status` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pv_adts` (
|
||
|
|
`pv_adt_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`pv_id` integer,
|
||
|
|
`pv_adt_num` varchar(255),
|
||
|
|
`pv_adt_code` varchar(255),
|
||
|
|
`locid` integer,
|
||
|
|
`docid` integer,
|
||
|
|
`reff_docid` integer,
|
||
|
|
`adm_docid` integer,
|
||
|
|
`cns_docid` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `pv_log` (
|
||
|
|
`pv_log_id` integer PRIMARY KEY AUTO_INCREMENT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `requests` (
|
||
|
|
`req_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`req_num` varchar(255) UNIQUE,
|
||
|
|
`req_altnum` varchar(255) UNIQUE,
|
||
|
|
`pat_id` integer,
|
||
|
|
`pv_id` integer,
|
||
|
|
`req_app` varchar(255),
|
||
|
|
`req_entity` varchar(255),
|
||
|
|
`req_entity_id` integer,
|
||
|
|
`loc_id` integer,
|
||
|
|
`priority` varchar(255),
|
||
|
|
`att_doid` integer,
|
||
|
|
`reff_docid` integer,
|
||
|
|
`adm_docid` integer,
|
||
|
|
`cns_docid` integer,
|
||
|
|
`entered_by` varchar(255),
|
||
|
|
`req_date` datetime,
|
||
|
|
`eff_date` datetime,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `req_comments` (
|
||
|
|
`req_com_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`req_id` integer,
|
||
|
|
`comment_text` text,
|
||
|
|
`user_id` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `req_atts` (
|
||
|
|
`req_att_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`req_id` integer,
|
||
|
|
`address` varchar(255),
|
||
|
|
`user_id` integer,
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `req_status` (
|
||
|
|
`req_status_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`req_id` integer,
|
||
|
|
`req_status` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `req_logs` (
|
||
|
|
`req_log_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`tbl_name` varchar(255),
|
||
|
|
`record_id` integer,
|
||
|
|
`fld_name` varchar(255),
|
||
|
|
`fld_value_prev` varchar(255),
|
||
|
|
`user_id` integer,
|
||
|
|
`site_id` integer,
|
||
|
|
`machine_id` integer,
|
||
|
|
`session_id` integer,
|
||
|
|
`app_id` integer,
|
||
|
|
`process_id` integer,
|
||
|
|
`webpage_id` integer,
|
||
|
|
`event_id` integer,
|
||
|
|
`act_id` integer,
|
||
|
|
`reason` varchar(255),
|
||
|
|
`log_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE `users` (
|
||
|
|
`user_id` integer PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
`username` varchar(255) UNIQUE,
|
||
|
|
`fullname` varchar(255),
|
||
|
|
`password` varchar(255),
|
||
|
|
`create_date` datetime,
|
||
|
|
`end_date` datetime,
|
||
|
|
`archive_date` datetime,
|
||
|
|
`del_date` datetime
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE `races` ADD FOREIGN KEY (`coding_sys_id`) REFERENCES `coding_sys` (`coding_sys_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `religions` ADD FOREIGN KEY (`coding_sys_id`) REFERENCES `coding_sys` (`coding_sys_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `ethnics` ADD FOREIGN KEY (`coding_sys_id`) REFERENCES `coding_sys` (`coding_sys_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `countries` ADD FOREIGN KEY (`coding_sys_id`) REFERENCES `coding_sys` (`coding_sys_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `patients` ADD FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `patients` ADD FOREIGN KEY (`race_id`) REFERENCES `races` (`race_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `patients` ADD FOREIGN KEY (`religion_id`) REFERENCES `religions` (`religion_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `patients` ADD FOREIGN KEY (`ethnic_id`) REFERENCES `ethnics` (`ethnic_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `pat_comments` ADD FOREIGN KEY (`pat_id`) REFERENCES `patients` (`pat_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `pat_identities` ADD FOREIGN KEY (`pat_id`) REFERENCES `patients` (`pat_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `pat_diagnose` ADD FOREIGN KEY (`pat_id`) REFERENCES `patients` (`pat_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `pat_visits` ADD FOREIGN KEY (`pat_id`) REFERENCES `patients` (`pat_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `pv_adts` ADD FOREIGN KEY (`pv_id`) REFERENCES `pat_visits` (`pv_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `requests` ADD FOREIGN KEY (`pat_id`) REFERENCES `patients` (`pat_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `requests` ADD FOREIGN KEY (`pv_id`) REFERENCES `pat_visits` (`pv_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `req_comments` ADD FOREIGN KEY (`req_id`) REFERENCES `requests` (`req_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `req_atts` ADD FOREIGN KEY (`req_id`) REFERENCES `requests` (`req_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `req_status` ADD FOREIGN KEY (`req_id`) REFERENCES `requests` (`req_id`);
|
||
|
|
|
||
|
|
ALTER TABLE `req_logs` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);
|