[sql] 122112121

Viewer

copydownloadembedprintName: 122112121
  1. CREATE TABLE `employee_has_system_role` (
  2.   `staff_id` VARCHAR(20),
  3.   `role_group_id` MEDIUMINT,
  4.   `created_at` TIMESTAMP,
  5.   PRIMARY KEY (`staff_id`, `role_group_id`),
  6. );
  7.  
  8. CREATE TABLE `caregiver_notifications` (
  9.   `id` INT,
  10.   `caregiver_cognito_id` VARCHAR(40),
  11.   `notification_category_id` SMALLINT,
  12.   `title` VARCHAR(255),
  13.   `message` VARCHAR(255),
  14.   `is_read` TINYINT(1),
  15.   `created_at` TIMESTAMP,
  16.   PRIMARY KEY (`id`)
  17. );
  18.  
  19. CREATE TABLE `device_notification_tracker` (
  20.   `device_name` VARCHAR(10),
  21.   `not_on_body_counter` TINYINT,
  22.   `not_on_body_notif_timestamp` TIMESTAMP,
  23.   `hr_buffer_counter` TINYINT,
  24.   `hr_notif_timestamp` TIMESTAMP,
  25.   `rr_buffer_counter` TINYINT,
  26.   `rr_notif_timestamp` TIMESTAMP,
  27.   `created_at` TIMESTAMP,
  28.   PRIMARY KEY (`device_name`)
  29. );
  30.  
  31. CREATE TABLE `patient_has_device` (
  32.   `id` INT,
  33.   `patient_cognito_id` VARCHAR(40),
  34.   `patient_id` VARCHAR(20),
  35.   `device_name` VARCHAR(10),
  36.   `start_of_use` TIMESTAMP,
  37.   `end_of_use` TIMESTAMP,
  38.   `is_active` TINYINT(1),
  39.   `created_at` TIMESTAMP,
  40.   PRIMARY KEY (`id`)
  41. );
  42.  
  43. CREATE TABLE `employees` (
  44.   `cognito_id` VARCHAR(40),
  45.   `staff_id` VARCHAR(20),
  46.   `first_name` VARCHAR(20),
  47.   `middle_name` VARCHAR(20),
  48.   `last_name` VARCHAR(20),
  49.   `email` VARCHAR(50),
  50.   `mobile_number` VARCHAR(40),
  51.   `mobile_country_code` VARCHAR(5),
  52.   `date_of_birth` DATE,
  53.   `gender` CHAR(1),
  54.   `is_active` TINYINT(1),
  55.   `device_token` VARCHAR(255),
  56.   `created_at` TIMESTAMP,
  57.   `deleted_at` TIMESTAMP,
  58.   PRIMARY KEY (`cognito_id`)
  59. );
  60.  
  61. CREATE TABLE `patient_assigned_to_physician` (
  62.   `id` INT,
  63.   `patient_id` VARCHAR(20),
  64.   `physician_id` VARCHAR(20),
  65.   `admin_id` VARCHAR(20),
  66.   `assigned_date` DATE,
  67.   `unassigned_date` DATE,
  68.   `is_bookmarked` TINYINT(1),
  69.   `created_at` TIMESTAMP,
  70.   PRIMARY KEY (`id`)
  71. );
  72.  
  73. -- CREATE TABLE `healthcare_organisations` (
  74. --   `account_id` VARCHAR(20),
  75. --   `sub_domain` VARCHAR(10),
  76. --   `hospital_name` VARCHAR(100),
  77. --   `website_url` VARCHAR(50),
  78. --   `address` VARCHAR(50),
  79. --   `city` VARCHAR(30),
  80. --   `state` VARCHAR(40),
  81. --   `postal_code` VARCHAR(10),
  82. --   `country` VARCHAR(20),
  83. --   `created_at` TIMESTAMP,
  84. --   PRIMARY KEY (`account_id`)
  85. -- );
  86.  
  87. CREATE TABLE `respiratory_health_records` (
  88.   `id` BIGINT(20),
  89.   `patient_cognito_id` VARCHAR(40),
  90.   `hr` TINYINT(3),
  91.   `rr` TINYINT(3),
  92.   `hrv` TINYINT(3),
  93.   `rrv` TINYINT(3),
  94.   `cough_occurrence` TINYINT(3),
  95.   `wheeze_occurrence` TINYINT(3),
  96.   `hr_status` TINYINT(1),
  97.   `rr_status` TINYINT(1),
  98.   `hrv_status` TINYINT(1),
  99.   `rrv_status` TINYINT(1),
  100.   `cough_status` TINYINT(1),
  101.   `wheeze_status` TINYINT(1),
  102.   `created_at` TIMESTAMP,
  103.   PRIMARY KEY (`id`)
  104. );
  105.  
  106. -- CREATE TABLE `healthcare_organisation_endpoints` (
  107. --   `account_id` VARCHAR(20),
  108. --   `mobile_cognito_user_pool_id` VARCHAR(255),
  109. --   `mobile_cognito_user_pool_region` VARCHAR(20),
  110. --   `web_cognito_user_pool_id` VARCHAR(255),
  111. --   `web_cognito_user_pool_region` VARCHAR(20),
  112. --   `mobile_cognito_identity_pool_id` VARCHAR(255),
  113. --   `mobile_cognito_identity_pool_region` VARCHAR(20),
  114. --   `web_cognito_identity_pool_id` VARCHAR(255),
  115. --   `web_cognito_identity_pool_region` VARCHAR(20),
  116. --   `mobile_api_gateway_endpoint` VARCHAR(255),
  117. --   `mobile_api_gateway_stage` VARCHAR(10),
  118. --   `web_api_gateway_endpoint` VARCHAR(255),
  119. --   `web_api_gateway_stage` VARCHAR(10),
  120. --   `mobile_api_gateway_region` VARCHAR(20),
  121. --   `iot_endpoint` VARCHAR(255),
  122. --   `iot_region` VARCHAR(20),
  123. --   `s3_endpoint` VARCHAR(255),
  124. --   `s3_bucket_name` VARCHAR(255),
  125. --   PRIMARY KEY (`account_id`)
  126. -- );
  127.  
  128. CREATE TABLE `historical_employee_has_system_role` (
  129.   `id` INT,
  130.   `staff_id` VARCHAR(20),
  131.   `role_group_id` MEDIUMINT,
  132.   `start_timestamp` TIMESTAMP,
  133.   `end_timestamp` TIMESTAMP,
  134.   `created_at` TIMESTAMP,
  135.   PRIMARY KEY (`id`)
  136. );
  137.  
  138. CREATE TABLE `patients` (
  139.   `cognito_id` VARCHAR(40),
  140.   `patient_id` VARCHAR(20),
  141.   `first_name` VARCHAR(20),
  142.   `middle_name` VARCHAR(20),
  143.   `last_name` VARCHAR(20),
  144.   `gender` TINYINT(1),
  145.   `date_of_birth` DATE,
  146.   `height` DECIMAL(5,2),
  147.   `weight` DECIMAL(5,2),
  148.   `blood_type` CHAR(3),
  149.   `registration_status` VARCHAR(10),
  150.   `lock_patient` TINYINT(1),
  151.   `created_at` TIMESTAMP,
  152.   PRIMARY KEY (`cognito_id`),
  153. );
  154.  
  155. CREATE TABLE `patient_has_caregivers` (
  156.   `id ` INT,
  157.   `patient_cognito_id` VARCHAR(40),
  158.   `caregiver_cognito_id` VARCHAR(40),
  159.   `is_deleted` TINYINT(1),
  160.   `created_at` TIMESTAMP,
  161.   `deleted_at` TIMESTAMP,
  162.   PRIMARY KEY (`id `)
  163. );
  164.  
  165. CREATE TABLE `recordings_shared_with_healthcare` (
  166.   `shared_recording_id` INT,
  167.   `recording_id` INT,
  168.   `is_deleted` TINYINT(1),
  169.   `is_bookmarked` TINYINT(1),
  170.   `created_at` TIMESTAMP,
  171.   `deleted_at` TIMESTAMP,
  172.   PRIMARY KEY (`shared_recording_id`)
  173. );
  174.  
  175. CREATE TABLE `user_notification_categories` (
  176.   `notification_category_id` SMALLINT,
  177.   `category_name` VARCHAR(40),
  178.   `description` VARCHAR(255),
  179.   `default_title` VARCHAR(255),
  180.   `default_message` VARCHAR(255),
  181.   `created_at` TIMESTAMP,
  182.   PRIMARY KEY (`notification_category_id`)
  183. );
  184.  
  185. CREATE TABLE `admins` (
  186.   `admin_id` VARCHAR(20),
  187.   `created_at` TIMESTAMP ,
  188.   PRIMARY KEY (`admin_id`)
  189. );
  190.  
  191. CREATE TABLE `patient_notifications` (
  192.   `id` INT,
  193.   `patient_cognito_id` VARHCAR(40),
  194.   `notification_category_id` SMALLINT,
  195.   `title` VARCHAR(255),
  196.   `message` VARCHAR(255),
  197.   `is_read` TINYINT(1),
  198.   `created_at` TIMESTAMP,
  199.   PRIMARY KEY (`id`)
  200. );
  201.  
  202. CREATE TABLE `departments` (
  203.   `department_id` TINYINT,
  204.   `department_name` VARCHAR(30),
  205.   `specialty` VARCHAR(40),
  206.   `created_at` TIMESTAMP,
  207.   PRIMARY KEY (`department_id`)
  208. );
  209.  
  210. CREATE TABLE `physicians` (
  211.   `physician_id` VARCHAR(20),
  212.   `medical_license_number` VARCHAR(20),
  213.   `licence_issued_date` DATE,
  214.   `created_at` TIMESTAMP,
  215.   PRIMARY KEY (`physician_id`)
  216. );
  217.  
  218. CREATE TABLE `auscultation_recordings` (
  219.   `recording_id` INT,
  220.   `patient_cognito_id` VARCHAR(40),
  221.   `recording_name` VARCHAR(40),
  222.   `wheeze_present` TINYINT(1),
  223.   `wheeze_result` VARCHAR(20),
  224.   `duration` TINYINT,
  225.   `file_timestamp` TIMESTAMP,
  226.   `is_shared` TINYINT(1),
  227.   `is_deleted` TINYINT(1),
  228.   `is_bookmarked` TINYINT(1),
  229.   `created_at` TIMESTAMP,
  230.   `deleted_at` TIMESTAMP,
  231.   PRIMARY KEY (`recording_id`)
  232. );
  233.  
  234. CREATE TABLE `system_groups` (
  235.   `role_group_id` MEDIUMINT,
  236.   `name` VARCHAR(20),
  237.   `created_at` TIMESTAMP,
  238.   PRIMARY KEY (`role_group_id`)
  239. );
  240.  
  241. CREATE TABLE `auscultation_remarks` (
  242.   `remarks_id` INT,
  243.   `shared_recording_id` INT,
  244.   `physician_id` INT,
  245.   `created_at` TIMESTAMP,
  246.   `deleted_at` TIMESTAMP,
  247.   PRIMARY KEY (`remarks_id`)
  248. );
  249.  
  250. CREATE TABLE `physician_notifications` (
  251.   `id` INT,
  252.   `physician_id` VARCHAR(20),
  253.   `notification_category_id` SMALLINT,
  254.   `title` VARCHAR(255),
  255.   `message` VARCHAR(255),
  256.   `is_read` TINYINT(1),
  257.   `created_at` TIMESTAMP,
  258.   PRIMARY KEY (`id`)
  259. );
  260.  
  261. CREATE TABLE `employee_designations` (
  262.   `designation_id` INT,
  263.   `staff_id` VARCHAR(20),
  264.   `department_id` TINYINT,
  265.   `designation_title` VARCHAR(20),
  266.   `rank` VARCHAR(20),
  267.   `created_at` TIMESTAMP,
  268.   `updated_at` TIMESTAMP,
  269.   PRIMARY KEY (`designation_id`),
  270. );
  271.  
  272. CREATE TABLE `physician_diagnoses` (
  273.   `id` INT,
  274.   `patient_assigned_to_physician_id` VARCHAR(20),
  275.   `diagnosis` VARCHAR(255),
  276.   `additional_info` VARCHAR(255),
  277.   `created_at` TIMESTAMP,
  278.   PRIMARY KEY (`id`)
  279. );
  280.  
  281. CREATE TABLE `admin_notifications` (
  282.   `id` INT,
  283.   `admin_id` VARCHAR(20),
  284.   `notification_category_id` SMALLINT,
  285.   `title` VARCHAR(255),
  286.   `message` VARCHAR(255),
  287.   `is_read` TINYINT(1),
  288.   `created_at` TIMESTAMP,
  289.   PRIMARY KEY (`id`)
  290. );
  291.  
  292. -- CREATE TABLE `mobile_user` (
  293. --   `cognito_id` VARCHAR(40),
  294. --   `hospital_id` VARCHAR(20),
  295. --   `device_name` VARCHAR(10),
  296. --   `first_name` VARCHAR(20),
  297. --   `middle_name` VARCHAR(20),
  298. --   `last_name` VARCHAR(20),
  299. --   `email` VARCHAR(50),
  300. --   `mobile_number` VARCHAR(40),
  301. --   `mobile_country_code` VARCHAR(5),
  302. --   `email_verified` TINYINT(1),
  303. --   `created_at` TIMESTAMP,
  304. --   PRIMARY KEY (`cognito_id`)
  305. -- );
  306.  
  307. -- CREATE TABLE `healthcare_organisation_endpoints_for_mobile` (
  308. --   `account_id` VARCHAR(20),
  309. --   `mobile_cognito_user_pool_id` VARCHAR(255),
  310. --   `mobile_cognito_user_pool_region` VARCHAR(20),
  311. --   `mobile_cognito_identity_pool_id` VARCHAR(255),
  312. --   `mobile_cognito_identity_pool_region` VARCHAR(20),
  313. --   `mobile_api_gateway_endpoint` VARCHAR(255),
  314. --   `mobile_api_gateway_stage` VARCHAR(10),
  315. --   `mobile_api_gateway_region` VARCHAR(20),
  316. --   PRIMARY KEY (`account_id`)
  317. -- );
  318.  
  319. -- CREATE TABLE `point_of_contact` (
  320. --   `account_id` VARCHAR(20),
  321. --   `email` VARCHAR(50),
  322. --   `first_name` VARCHAR(20),
  323. --   `middle_name` VARCHAR(20),
  324. --   `last_name` VARCHAR(20),
  325. --   `mobile_number` VARCHAR(40),
  326. --   `mobile_country_code` VARCHAR(5),
  327. --   `created_at` TIMESTAMP,
  328. --   KEY `PK, FK` (`account_id`)
  329. -- );
  330.  
  331. CREATE TABLE `healthcare_notification_categories` (
  332.   `notification_category_id` SMALLINT,
  333.   `category_name` VARCHAR(40),
  334.   `description` VARCHAR(255),
  335.   `default_title` VARCHAR(255),
  336.   `default_message` VARCHAR(255),
  337.   `created_at` TIMESTAMP,
  338.   PRIMARY KEY (`notification_category_id`),
  339. );
  340.  
  341. CREATE TABLE `default_system_permissions` (
  342.   `permission_id` MEDIUMINT,
  343.   `role_group_id` MEDIUMINT,
  344.   `create_admin_account` TINYINT(1),
  345.   `create_physician_account` TINYINT(1),
  346.   `verify_patient_account` TINYINT(1),
  347.   `view_admin_profile` TINYINT(1),
  348.   `view_physician_profile` TINYINT(1),
  349.   `view_patient_profile` TINYINT(1),
  350.   `view_patient_auscultation_recordings` TINYINT(1),
  351.   `download_patient_auscultation_recordings` TINYINT(1),
  352.   `delete_patient_auscultation_recordings` TINYINT(1),
  353.   `edit_admin_profile` TINYINT(1),
  354.   `edit_physician_profile` TINYINT(1),
  355.   `edit_patient_profile` TINYINT(1),
  356.   `delete_admin_profile` TINYINT(1),
  357.   `delete_physician_profile` TINYINT(1),
  358.   `delete_patient_profile` TINYINT(1),
  359.   `assign_physician_to_patient` TINYINT(1),
  360.   `unassign_physician_to_patient` TINYINT(1),
  361.   `assign_device_to_patient` TINYINT(1),
  362.   `unassign_device_to_patient` TINYINT(1),
  363.   `create_patient_treatment_case` TINYINT(1),
  364.   `close_patient_treatment_case` TINYINT(1),
  365.   `schedule_patient_consultation` TINYINT(1),
  366.   `reschedule_patient_consultation` TINYINT(1),
  367.   `cancel_patient_consultation` TINYINT(1),
  368.   `export_patient_data` TINYINT(1),
  369.   `created_at` TIMESTAMP,
  370.   PRIMARY KEY (`permission_id`)
  371. );
  372.  
  373. CREATE TABLE `caregivers` (
  374.   `cognito_id` VARCHAR(40),
  375.   `first_name` VARCHAR(20),
  376.   `middle_name` VARCHAR(20),
  377.   `last_name` VARCHAR(20),
  378.   `email` VARCHAR(50),
  379.   `mobile_number` VARCHAR(40),
  380.   `mobile_country_code` VARCHAR(5),
  381.   `address` VARCHAR(50),
  382.   `city` VARCHAR(30),
  383.   `state` VARCHAR(30),
  384.   `postal_code` VARCHAR(10),
  385.   `gender` TINYINT(1),
  386.   `device_token` VARCHAR(255),
  387.   `device_os` VARCHAR(10),
  388.   `email_verified` TINYINT(1),
  389.   `created_at` TIMESTAMP,
  390.   PRIMARY KEY (`cognito_id`)
  391. );
  392.  
  393.  
  394. ALTER TABLE employee_has_system_role ADD CONSTRAINT employee_has_system_role__employees__staff_id FOREIGN KEY (staff_id) REFERENCES employees(staff_id) ON UPDATE CASCADE;
  395. ALTER TABLE employee_has_system_role ADD CONSTRAINT employee_has_system_role__employees__role_group_id FOREIGN KEY (role_group_id) REFERENCES system_groups(role_group_id);
  396.  
  397. ALTER TABLE caregiver_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES user_notification_categories(notification_category_id);
  398. ALTER TABLE caregiver_notifications ADD FOREIGN KEY (caregiver_cognito_id) REFERENCES caregivers(cognito_id);
  399.  
  400. ALTER TABLE patient_has_device ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
  401. ALTER TABLE patient_has_device ADD FOREIGN KEY (patient_id) REFERENCES patients(patient_id);
  402.  
  403. ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (patient_id) REFERENCES patients(patient_id);
  404. ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
  405. ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (admin_id) REFERENCES admins(admin_id);
  406.  
  407. ALTER TABLE respiratory_health_records ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
  408.  
  409. ALTER TABLE historical_employee_has_system_role ADD FOREIGN KEY (staff_id) REFERENCES employees(notification_category_id);
  410. ALTER TABLE historical_employee_has_system_role ADD FOREIGN KEY (role_group_id) REFERENCES system_groups(role_group_id);
  411.  
  412. ALTER TABLE patient_has_caregivers ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
  413. ALTER TABLE patient_has_caregivers ADD FOREIGN KEY (caregiver_cognito_id) REFERENCES caregivers(cognito_id);
  414.  
  415. ALTER TABLE recordings_shared_with_healthcare ADD FOREIGN KEY (recording_id) REFERENCES auscultation_recordings(recording_id);
  416.  
  417. ALTER TABLE patient_notifications ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(notification_category_id);
  418. ALTER TABLE patient_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES user_notification_categories(notification_category_id);
  419.  
  420. ALTER TABLE physicians ADD FOREIGN KEY (physician_id) REFERENCES patient_assigned_to_physician(physician_id);
  421.  
  422. ALTER TABLE auscultation_recordings ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
  423.  
  424. ALTER TABLE auscultation_remarks ADD FOREIGN KEY (shared_recording_id) REFERENCES recordings_shared_with_healthcare(shared_recording_id);
  425. ALTER TABLE auscultation_remarks ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
  426.  
  427. ALTER TABLE physician_notifications ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
  428. ALTER TABLE physician_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES healthcare_notification_categories(notification_category_id);
  429.  
  430. ALTER TABLE employee_designations ADD FOREIGN KEY (staff_id) REFERENCES employees(notification_category_id);
  431. ALTER TABLE employee_designations ADD FOREIGN KEY (staff_id) REFERENCES admins(admin_id);
  432. ALTER TABLE employee_designations ADD FOREIGN KEY (department_id) REFERENCES departments(notification_category_id);
  433.  
  434. ALTER TABLE physician_diagnoses ADD FOREIGN KEY (patient_assigned_to_physician_id) REFERENCES patient_assigned_to_physician(id);
  435.  
  436. ALTER TABLE admin_notifications ADD FOREIGN KEY (admin_id) REFERENCES patient_assigned_to_physician(id);
  437. ALTER TABLE admin_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES healthcare_notification_categories(notification_category_id);
  438.  
  439. ALTER TABLE default_system_permissions ADD FOREIGN KEY (role_group_id) REFERENCES system_groups(role_group_id);

Editor

You can edit this paste and save as new:


File Description
  • 122112121
  • Paste Code
  • 14 Jun-2021
  • 14.29 Kb
You can Share it: