[sql] 122112121
Viewer
- CREATE TABLE `employee_has_system_role` (
- `staff_id` VARCHAR(20),
- `role_group_id` MEDIUMINT,
- `created_at` TIMESTAMP,
- PRIMARY KEY (`staff_id`, `role_group_id`),
- );
- CREATE TABLE `caregiver_notifications` (
- `id` INT,
- `caregiver_cognito_id` VARCHAR(40),
- `notification_category_id` SMALLINT,
- `title` VARCHAR(255),
- `message` VARCHAR(255),
- `is_read` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `device_notification_tracker` (
- `device_name` VARCHAR(10),
- `not_on_body_counter` TINYINT,
- `not_on_body_notif_timestamp` TIMESTAMP,
- `hr_buffer_counter` TINYINT,
- `hr_notif_timestamp` TIMESTAMP,
- `rr_buffer_counter` TINYINT,
- `rr_notif_timestamp` TIMESTAMP,
- `created_at` TIMESTAMP,
- PRIMARY KEY (`device_name`)
- );
- CREATE TABLE `patient_has_device` (
- `id` INT,
- `patient_cognito_id` VARCHAR(40),
- `patient_id` VARCHAR(20),
- `device_name` VARCHAR(10),
- `start_of_use` TIMESTAMP,
- `end_of_use` TIMESTAMP,
- `is_active` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `employees` (
- `cognito_id` VARCHAR(40),
- `staff_id` VARCHAR(20),
- `first_name` VARCHAR(20),
- `middle_name` VARCHAR(20),
- `last_name` VARCHAR(20),
- `email` VARCHAR(50),
- `mobile_number` VARCHAR(40),
- `mobile_country_code` VARCHAR(5),
- `date_of_birth` DATE,
- `gender` CHAR(1),
- `is_active` TINYINT(1),
- `device_token` VARCHAR(255),
- `created_at` TIMESTAMP,
- `deleted_at` TIMESTAMP,
- PRIMARY KEY (`cognito_id`)
- );
- CREATE TABLE `patient_assigned_to_physician` (
- `id` INT,
- `patient_id` VARCHAR(20),
- `physician_id` VARCHAR(20),
- `admin_id` VARCHAR(20),
- `assigned_date` DATE,
- `unassigned_date` DATE,
- `is_bookmarked` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- -- CREATE TABLE `healthcare_organisations` (
- -- `account_id` VARCHAR(20),
- -- `sub_domain` VARCHAR(10),
- -- `hospital_name` VARCHAR(100),
- -- `website_url` VARCHAR(50),
- -- `address` VARCHAR(50),
- -- `city` VARCHAR(30),
- -- `state` VARCHAR(40),
- -- `postal_code` VARCHAR(10),
- -- `country` VARCHAR(20),
- -- `created_at` TIMESTAMP,
- -- PRIMARY KEY (`account_id`)
- -- );
- CREATE TABLE `respiratory_health_records` (
- `id` BIGINT(20),
- `patient_cognito_id` VARCHAR(40),
- `hr` TINYINT(3),
- `rr` TINYINT(3),
- `hrv` TINYINT(3),
- `rrv` TINYINT(3),
- `cough_occurrence` TINYINT(3),
- `wheeze_occurrence` TINYINT(3),
- `hr_status` TINYINT(1),
- `rr_status` TINYINT(1),
- `hrv_status` TINYINT(1),
- `rrv_status` TINYINT(1),
- `cough_status` TINYINT(1),
- `wheeze_status` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- -- CREATE TABLE `healthcare_organisation_endpoints` (
- -- `account_id` VARCHAR(20),
- -- `mobile_cognito_user_pool_id` VARCHAR(255),
- -- `mobile_cognito_user_pool_region` VARCHAR(20),
- -- `web_cognito_user_pool_id` VARCHAR(255),
- -- `web_cognito_user_pool_region` VARCHAR(20),
- -- `mobile_cognito_identity_pool_id` VARCHAR(255),
- -- `mobile_cognito_identity_pool_region` VARCHAR(20),
- -- `web_cognito_identity_pool_id` VARCHAR(255),
- -- `web_cognito_identity_pool_region` VARCHAR(20),
- -- `mobile_api_gateway_endpoint` VARCHAR(255),
- -- `mobile_api_gateway_stage` VARCHAR(10),
- -- `web_api_gateway_endpoint` VARCHAR(255),
- -- `web_api_gateway_stage` VARCHAR(10),
- -- `mobile_api_gateway_region` VARCHAR(20),
- -- `iot_endpoint` VARCHAR(255),
- -- `iot_region` VARCHAR(20),
- -- `s3_endpoint` VARCHAR(255),
- -- `s3_bucket_name` VARCHAR(255),
- -- PRIMARY KEY (`account_id`)
- -- );
- CREATE TABLE `historical_employee_has_system_role` (
- `id` INT,
- `staff_id` VARCHAR(20),
- `role_group_id` MEDIUMINT,
- `start_timestamp` TIMESTAMP,
- `end_timestamp` TIMESTAMP,
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `patients` (
- `cognito_id` VARCHAR(40),
- `patient_id` VARCHAR(20),
- `first_name` VARCHAR(20),
- `middle_name` VARCHAR(20),
- `last_name` VARCHAR(20),
- `gender` TINYINT(1),
- `date_of_birth` DATE,
- `height` DECIMAL(5,2),
- `weight` DECIMAL(5,2),
- `blood_type` CHAR(3),
- `registration_status` VARCHAR(10),
- `lock_patient` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`cognito_id`),
- );
- CREATE TABLE `patient_has_caregivers` (
- `id ` INT,
- `patient_cognito_id` VARCHAR(40),
- `caregiver_cognito_id` VARCHAR(40),
- `is_deleted` TINYINT(1),
- `created_at` TIMESTAMP,
- `deleted_at` TIMESTAMP,
- PRIMARY KEY (`id `)
- );
- CREATE TABLE `recordings_shared_with_healthcare` (
- `shared_recording_id` INT,
- `recording_id` INT,
- `is_deleted` TINYINT(1),
- `is_bookmarked` TINYINT(1),
- `created_at` TIMESTAMP,
- `deleted_at` TIMESTAMP,
- PRIMARY KEY (`shared_recording_id`)
- );
- CREATE TABLE `user_notification_categories` (
- `notification_category_id` SMALLINT,
- `category_name` VARCHAR(40),
- `description` VARCHAR(255),
- `default_title` VARCHAR(255),
- `default_message` VARCHAR(255),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`notification_category_id`)
- );
- CREATE TABLE `admins` (
- `admin_id` VARCHAR(20),
- `created_at` TIMESTAMP ,
- PRIMARY KEY (`admin_id`)
- );
- CREATE TABLE `patient_notifications` (
- `id` INT,
- `patient_cognito_id` VARHCAR(40),
- `notification_category_id` SMALLINT,
- `title` VARCHAR(255),
- `message` VARCHAR(255),
- `is_read` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `departments` (
- `department_id` TINYINT,
- `department_name` VARCHAR(30),
- `specialty` VARCHAR(40),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`department_id`)
- );
- CREATE TABLE `physicians` (
- `physician_id` VARCHAR(20),
- `medical_license_number` VARCHAR(20),
- `licence_issued_date` DATE,
- `created_at` TIMESTAMP,
- PRIMARY KEY (`physician_id`)
- );
- CREATE TABLE `auscultation_recordings` (
- `recording_id` INT,
- `patient_cognito_id` VARCHAR(40),
- `recording_name` VARCHAR(40),
- `wheeze_present` TINYINT(1),
- `wheeze_result` VARCHAR(20),
- `duration` TINYINT,
- `file_timestamp` TIMESTAMP,
- `is_shared` TINYINT(1),
- `is_deleted` TINYINT(1),
- `is_bookmarked` TINYINT(1),
- `created_at` TIMESTAMP,
- `deleted_at` TIMESTAMP,
- PRIMARY KEY (`recording_id`)
- );
- CREATE TABLE `system_groups` (
- `role_group_id` MEDIUMINT,
- `name` VARCHAR(20),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`role_group_id`)
- );
- CREATE TABLE `auscultation_remarks` (
- `remarks_id` INT,
- `shared_recording_id` INT,
- `physician_id` INT,
- `created_at` TIMESTAMP,
- `deleted_at` TIMESTAMP,
- PRIMARY KEY (`remarks_id`)
- );
- CREATE TABLE `physician_notifications` (
- `id` INT,
- `physician_id` VARCHAR(20),
- `notification_category_id` SMALLINT,
- `title` VARCHAR(255),
- `message` VARCHAR(255),
- `is_read` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `employee_designations` (
- `designation_id` INT,
- `staff_id` VARCHAR(20),
- `department_id` TINYINT,
- `designation_title` VARCHAR(20),
- `rank` VARCHAR(20),
- `created_at` TIMESTAMP,
- `updated_at` TIMESTAMP,
- PRIMARY KEY (`designation_id`),
- );
- CREATE TABLE `physician_diagnoses` (
- `id` INT,
- `patient_assigned_to_physician_id` VARCHAR(20),
- `diagnosis` VARCHAR(255),
- `additional_info` VARCHAR(255),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- CREATE TABLE `admin_notifications` (
- `id` INT,
- `admin_id` VARCHAR(20),
- `notification_category_id` SMALLINT,
- `title` VARCHAR(255),
- `message` VARCHAR(255),
- `is_read` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`id`)
- );
- -- CREATE TABLE `mobile_user` (
- -- `cognito_id` VARCHAR(40),
- -- `hospital_id` VARCHAR(20),
- -- `device_name` VARCHAR(10),
- -- `first_name` VARCHAR(20),
- -- `middle_name` VARCHAR(20),
- -- `last_name` VARCHAR(20),
- -- `email` VARCHAR(50),
- -- `mobile_number` VARCHAR(40),
- -- `mobile_country_code` VARCHAR(5),
- -- `email_verified` TINYINT(1),
- -- `created_at` TIMESTAMP,
- -- PRIMARY KEY (`cognito_id`)
- -- );
- -- CREATE TABLE `healthcare_organisation_endpoints_for_mobile` (
- -- `account_id` VARCHAR(20),
- -- `mobile_cognito_user_pool_id` VARCHAR(255),
- -- `mobile_cognito_user_pool_region` VARCHAR(20),
- -- `mobile_cognito_identity_pool_id` VARCHAR(255),
- -- `mobile_cognito_identity_pool_region` VARCHAR(20),
- -- `mobile_api_gateway_endpoint` VARCHAR(255),
- -- `mobile_api_gateway_stage` VARCHAR(10),
- -- `mobile_api_gateway_region` VARCHAR(20),
- -- PRIMARY KEY (`account_id`)
- -- );
- -- CREATE TABLE `point_of_contact` (
- -- `account_id` VARCHAR(20),
- -- `email` VARCHAR(50),
- -- `first_name` VARCHAR(20),
- -- `middle_name` VARCHAR(20),
- -- `last_name` VARCHAR(20),
- -- `mobile_number` VARCHAR(40),
- -- `mobile_country_code` VARCHAR(5),
- -- `created_at` TIMESTAMP,
- -- KEY `PK, FK` (`account_id`)
- -- );
- CREATE TABLE `healthcare_notification_categories` (
- `notification_category_id` SMALLINT,
- `category_name` VARCHAR(40),
- `description` VARCHAR(255),
- `default_title` VARCHAR(255),
- `default_message` VARCHAR(255),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`notification_category_id`),
- );
- CREATE TABLE `default_system_permissions` (
- `permission_id` MEDIUMINT,
- `role_group_id` MEDIUMINT,
- `create_admin_account` TINYINT(1),
- `create_physician_account` TINYINT(1),
- `verify_patient_account` TINYINT(1),
- `view_admin_profile` TINYINT(1),
- `view_physician_profile` TINYINT(1),
- `view_patient_profile` TINYINT(1),
- `view_patient_auscultation_recordings` TINYINT(1),
- `download_patient_auscultation_recordings` TINYINT(1),
- `delete_patient_auscultation_recordings` TINYINT(1),
- `edit_admin_profile` TINYINT(1),
- `edit_physician_profile` TINYINT(1),
- `edit_patient_profile` TINYINT(1),
- `delete_admin_profile` TINYINT(1),
- `delete_physician_profile` TINYINT(1),
- `delete_patient_profile` TINYINT(1),
- `assign_physician_to_patient` TINYINT(1),
- `unassign_physician_to_patient` TINYINT(1),
- `assign_device_to_patient` TINYINT(1),
- `unassign_device_to_patient` TINYINT(1),
- `create_patient_treatment_case` TINYINT(1),
- `close_patient_treatment_case` TINYINT(1),
- `schedule_patient_consultation` TINYINT(1),
- `reschedule_patient_consultation` TINYINT(1),
- `cancel_patient_consultation` TINYINT(1),
- `export_patient_data` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`permission_id`)
- );
- CREATE TABLE `caregivers` (
- `cognito_id` VARCHAR(40),
- `first_name` VARCHAR(20),
- `middle_name` VARCHAR(20),
- `last_name` VARCHAR(20),
- `email` VARCHAR(50),
- `mobile_number` VARCHAR(40),
- `mobile_country_code` VARCHAR(5),
- `address` VARCHAR(50),
- `city` VARCHAR(30),
- `state` VARCHAR(30),
- `postal_code` VARCHAR(10),
- `gender` TINYINT(1),
- `device_token` VARCHAR(255),
- `device_os` VARCHAR(10),
- `email_verified` TINYINT(1),
- `created_at` TIMESTAMP,
- PRIMARY KEY (`cognito_id`)
- );
- 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;
- 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);
- ALTER TABLE caregiver_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES user_notification_categories(notification_category_id);
- ALTER TABLE caregiver_notifications ADD FOREIGN KEY (caregiver_cognito_id) REFERENCES caregivers(cognito_id);
- ALTER TABLE patient_has_device ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
- ALTER TABLE patient_has_device ADD FOREIGN KEY (patient_id) REFERENCES patients(patient_id);
- ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (patient_id) REFERENCES patients(patient_id);
- ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
- ALTER TABLE patient_assigned_to_physician ADD FOREIGN KEY (admin_id) REFERENCES admins(admin_id);
- ALTER TABLE respiratory_health_records ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
- ALTER TABLE historical_employee_has_system_role ADD FOREIGN KEY (staff_id) REFERENCES employees(notification_category_id);
- ALTER TABLE historical_employee_has_system_role ADD FOREIGN KEY (role_group_id) REFERENCES system_groups(role_group_id);
- ALTER TABLE patient_has_caregivers ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
- ALTER TABLE patient_has_caregivers ADD FOREIGN KEY (caregiver_cognito_id) REFERENCES caregivers(cognito_id);
- ALTER TABLE recordings_shared_with_healthcare ADD FOREIGN KEY (recording_id) REFERENCES auscultation_recordings(recording_id);
- ALTER TABLE patient_notifications ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(notification_category_id);
- ALTER TABLE patient_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES user_notification_categories(notification_category_id);
- ALTER TABLE physicians ADD FOREIGN KEY (physician_id) REFERENCES patient_assigned_to_physician(physician_id);
- ALTER TABLE auscultation_recordings ADD FOREIGN KEY (patient_cognito_id) REFERENCES patients(cognito_id);
- ALTER TABLE auscultation_remarks ADD FOREIGN KEY (shared_recording_id) REFERENCES recordings_shared_with_healthcare(shared_recording_id);
- ALTER TABLE auscultation_remarks ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
- ALTER TABLE physician_notifications ADD FOREIGN KEY (physician_id) REFERENCES physicians(physician_id);
- ALTER TABLE physician_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES healthcare_notification_categories(notification_category_id);
- ALTER TABLE employee_designations ADD FOREIGN KEY (staff_id) REFERENCES employees(notification_category_id);
- ALTER TABLE employee_designations ADD FOREIGN KEY (staff_id) REFERENCES admins(admin_id);
- ALTER TABLE employee_designations ADD FOREIGN KEY (department_id) REFERENCES departments(notification_category_id);
- ALTER TABLE physician_diagnoses ADD FOREIGN KEY (patient_assigned_to_physician_id) REFERENCES patient_assigned_to_physician(id);
- ALTER TABLE admin_notifications ADD FOREIGN KEY (admin_id) REFERENCES patient_assigned_to_physician(id);
- ALTER TABLE admin_notifications ADD FOREIGN KEY (notification_category_id) REFERENCES healthcare_notification_categories(notification_category_id);
- 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:
Latest Code Pastes