tables for healcare project

healcare.sql

CREATE TABLE `healthcare_center_employees` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `ssnNumber` char(20) NOT NULL,
  `address` varchar(200) NOT NULL,
  `phone` char(20) NOT NULL,
  `email` varchar(200) NOT NULL,
  `pastAndCurrentJobTitle` varchar(100) NOT NULL,
  `salary` double(16, 4) NOT NULL,
  `benefits` text NOT NULL,
  `contactType` tinyint(4) NOT NULL DEFAULT 0,
  `contactTerm` varchar(200) NOT NULL,
  `reviews` text NOT NULL,
  `officeInformationId` int(11) unsigned NOT NULL,
  `workScheduleId` int(11) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uk_name` (`name`(20)),
  UNIQUE KEY `uk_ssnNumber` (`ssnNumber`),
  KEY `idx_phone` (`phone`)
);


CREATE TABLE `healthcare_center_employee_office_information` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `office` varchar(100) NOT NULL,
  `officePhone` varchar(100) NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_employee_work_schedule` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `beginTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_patient` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `ssnNumber` char(20) NOT NULL,
  `roomId` int(11) unsigned NOT NULL,
  `address` varchar(200) NOT NULL,
  `phone` char(20) NOT NULL,
  `email` varchar(200) NOT NULL,
  `spouseInformation` text NOT NULL,
  `primaryDoctorId` int(11) unsigned NOT NULL,
  `healcareInformationId` int(11) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`(20)),
  UNIQUE KEY `uk_ssnNumber` (`ssnNumber`),
  KEY `idx_phone` (`phone`)
);


CREATE TABLE `healthcare_center_patient_rooms` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `location` varchar(100) NOT NULL,
  `capacity` int(11) unsigned NOT NULL,
  `status` tinyint(4) NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_equipments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `equipment_type` tinyint(4) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE `healthcare_center_patient_room_equipments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `patientRoomId` int(11) unsigned NOT NULL,
  `equipmentId` int(11) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patientRoomId` (`patientRoomId`),
  KEY `idx_equipmentId` (`equipmentId`)
);


CREATE TABLE `healthcare_center_patient_room_schedule` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `patientRoomId` int(11) unsigned NOT NULL,
  `patientId` int(11) unsigned NOT NULL,
  `employeeId` int(11) unsigned NOT NULL,
  `beginTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patientId_endTime` (`patientId`, `endTime`)
);


CREATE TABLE `healthcare_center_patient_health_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `patientId` int(11) unsigned NOT NULL,
  `weight` double(16, 4) NOT NULL,
  `height` double(16, 4) NOT NULL,
  `vitals` text NOT NULL,
  `procedures` text NOT NULL,
  `physicians` varchar(100) NOT NULL,
  `referralDoctor` varchar(100) NOT NULL,
  `medicationInformation` text NOT NULL,
  `hospitalizationInformation` text NOT NULL,
  `dischargeInformation` text NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patientId` (`patientId`)
);


CREATE TABLE `healthcare_center_costs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `medication` text NOT NULL,
  `procedures` text NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_billing` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `billingType` tinyint(4) NOT NULL,
  `itemId` int(11) unsigned NOT NULL,
  `paymentMethod` tinyint(4) NOT NULL,
  `payorId` int(11) unsigned NOT NULL,
  `paymentTime` datetime NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_insurance_information` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `healthcareCompanyName` varchar(100) NOT NULL,
  `phone` char(20) NOT NULL,
  `contactInformation` varchar(200) NOT NULL,
  `insuranceCoverage` double(16, 4) NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);


CREATE TABLE `healthcare_center_vistors` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `ssnNumber` char(20) NOT NULL,
  `birthday` date NOT NULL,
  `picurl` char(200) NOT NULL,
  `visitTimes` tinyint(4) NOT NULL DEFAULT 0,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);



CREATE TABLE `healthcare_center_vistor_event` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `visitorId` int(11) unsigned NOT NULL,
  `patientId` int(11) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `_updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);