· 6 years ago · Oct 02, 2019, 04:18 PM
1CREATE SCHEMA IF NOT EXISTS `hospitaldb` DEFAULT CHARACTER SET latin1 ;
2USE `hospitaldb` ;
3
4CREATE TABLE IF NOT EXISTS `hospitaldb`.`departments` (
5 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
6 `name` VARCHAR(100) NULL DEFAULT NULL,
7 `description` TEXT NULL DEFAULT NULL,
8 PRIMARY KEY (`id`))
9ENGINE = InnoDB
10DEFAULT CHARACTER SET = latin1;
11
12CREATE TABLE IF NOT EXISTS `hospitaldb`.`specializations` (
13 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
14 `name` VARCHAR(100) NOT NULL,
15 PRIMARY KEY (`id`))
16ENGINE = InnoDB
17DEFAULT CHARACTER SET = latin1;
18
19CREATE TABLE IF NOT EXISTS `hospitaldb`.`doctors` (
20 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
21 `specialization_id` INT UNSIGNED NULL,
22 `price` DECIMAL(5,2) NULL,
23 PRIMARY KEY (`id`),
24 INDEX `fk_doctors_1_idx` (`specialization_id` ASC),
25 CONSTRAINT `fk_doctors_1`
26 FOREIGN KEY (`specialization_id`)
27 REFERENCES `hospitaldb`.`specializations` (`id`)
28 ON DELETE NO ACTION
29 ON UPDATE NO ACTION)
30ENGINE = InnoDB;
31
32CREATE TABLE IF NOT EXISTS `hospitaldb`.`employees` (
33 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
34 `first_name` VARCHAR(100) NULL DEFAULT NULL,
35 `last_name` VARCHAR(100) NULL DEFAULT NULL,
36 `organization_email` VARCHAR(100) NULL DEFAULT NULL,
37 `department_id` INT(10) UNSIGNED NULL DEFAULT NULL,
38 `reporting_line_id` INT(10) UNSIGNED NULL DEFAULT NULL,
39 `doctor_id` INT(10) UNSIGNED NULL,
40 `service_start` DATE NULL,
41 `service_end` DATE NULL,
42 `salary` INT NULL,
43 `position` VARCHAR(45) NULL,
44 `emp_type` VARCHAR(45) NULL,
45 `address` TEXT NULL,
46 PRIMARY KEY (`id`),
47 INDEX `fk_employees_3_idx` (`department_id` ASC),
48 INDEX `fk_employees_5_idx` (`reporting_line_id` ASC),
49 INDEX `fk_employees_6_idx` (`doctor_id` ASC),
50 CONSTRAINT `fk_employees_3`
51 FOREIGN KEY (`department_id`)
52 REFERENCES `hospitaldb`.`departments` (`id`)
53 ON DELETE NO ACTION
54 ON UPDATE NO ACTION,
55 CONSTRAINT `fk_employees_5`
56 FOREIGN KEY (`reporting_line_id`)
57 REFERENCES `hospitaldb`.`employees` (`id`)
58 ON DELETE NO ACTION
59 ON UPDATE NO ACTION,
60 CONSTRAINT `fk_employees_6`
61 FOREIGN KEY (`doctor_id`)
62 REFERENCES `hospitaldb`.`doctors` (`id`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB
66DEFAULT CHARACTER SET = latin1;
67
68CREATE TABLE IF NOT EXISTS `hospitaldb`.`attendences` (
69 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
70 `entrance_time` DATETIME NULL,
71 `exit_time` DATETIME NULL,
72 `employee_id` INT UNSIGNED NULL,
73 PRIMARY KEY (`id`),
74 INDEX `fk_attendences_1_idx` (`employee_id` ASC),
75 CONSTRAINT `fk_attendences_1`
76 FOREIGN KEY (`employee_id`)
77 REFERENCES `hospitaldb`.`employees` (`id`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION)
80ENGINE = InnoDB;
81
82CREATE TABLE IF NOT EXISTS `hospitaldb`.`users` (
83 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
84 `email` VARCHAR(100) NULL,
85 `password` VARCHAR(30) NULL,
86 `last_login` DATETIME NULL,
87 `employee_id` INT UNSIGNED NULL,
88 PRIMARY KEY (`id`),
89 INDEX `fk_users_1_idx` (`employee_id` ASC),
90 CONSTRAINT `fk_users_1`
91 FOREIGN KEY (`employee_id`)
92 REFERENCES `hospitaldb`.`employees` (`id`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97CREATE TABLE IF NOT EXISTS `hospitaldb`.`patients` (
98 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
99 `first_name` VARCHAR(100) NULL,
100 `last_name` VARCHAR(100) NULL,
101 `hn` VARCHAR(40) NULL,
102 `address` TEXT NULL,
103 `date_of_birth` DATETIME NULL,
104 `national_id_no` VARCHAR(30) NULL,
105 PRIMARY KEY (`id`))
106ENGINE = InnoDB;
107
108CREATE TABLE IF NOT EXISTS `hospitaldb`.`appointments` (
109 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
110 `visiting_time` DATETIME NULL,
111 `patient_id` INT UNSIGNED NULL,
112 `doctor_id` INT UNSIGNED NULL,
113 `symptoms` TEXT NULL,
114 PRIMARY KEY (`id`),
115 INDEX `fk_appointments_1_idx` (`patient_id` ASC),
116 INDEX `fk_appointments_2_idx` (`doctor_id` ASC),
117 CONSTRAINT `fk_appointments_1`
118 FOREIGN KEY (`patient_id`)
119 REFERENCES `hospitaldb`.`patients` (`id`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION,
122 CONSTRAINT `fk_appointments_2`
123 FOREIGN KEY (`doctor_id`)
124 REFERENCES `hospitaldb`.`doctors` (`id`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129CREATE TABLE IF NOT EXISTS `hospitaldb`.`admission` (
130 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
131 `admission_time` DATETIME NULL,
132 `patient_id` INT UNSIGNED NULL,
133 `symptoms` TEXT NULL,
134 `remarks` TEXT NULL,
135 `release_time` DATETIME NULL,
136 PRIMARY KEY (`id`),
137 INDEX `fk_admission_1_idx` (`patient_id` ASC),
138 CONSTRAINT `fk_admission_1`
139 FOREIGN KEY (`patient_id`)
140 REFERENCES `hospitaldb`.`patients` (`id`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION)
143ENGINE = InnoDB;
144
145CREATE TABLE IF NOT EXISTS `hospitaldb`.`beds` (
146 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
147 `number` VARCHAR(20) NULL,
148 `bed_type` VARCHAR(45) NULL,
149 `bed_price` INT NULL,
150 PRIMARY KEY (`id`))
151ENGINE = InnoDB;
152
153CREATE TABLE IF NOT EXISTS `hospitaldb`.`medicines` (
154 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
155 `mg` INT(5) NULL,
156 `medicine_type` ENUM('tablet', 'capsule', 'iv', 'syrup', 'inhaler', 'other') NULL,
157 `medicine_price` INT NULL,
158 `name` VARCHAR(100) NULL,
159 PRIMARY KEY (`id`))
160ENGINE = InnoDB;
161
162CREATE TABLE IF NOT EXISTS `hospitaldb`.`medicine_inventory` (
163 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
164 `medicine_id` INT UNSIGNED NULL,
165 `unit` INT NULL,
166 PRIMARY KEY (`id`),
167 INDEX `fk_medicine_inventory_1_idx` (`medicine_id` ASC),
168 CONSTRAINT `fk_medicine_inventory_1`
169 FOREIGN KEY (`medicine_id`)
170 REFERENCES `hospitaldb`.`medicines` (`id`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION)
173ENGINE = InnoDB;
174
175CREATE TABLE IF NOT EXISTS `hospitaldb`.`doctor_visits` (
176 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
177 `doctor_id` INT UNSIGNED NULL,
178 `patient_id` INT UNSIGNED NULL,
179 `visiting_time` DATETIME NULL,
180 PRIMARY KEY (`id`),
181 INDEX `fk_doctor_visits_2_idx` (`patient_id` ASC),
182 INDEX `fk_doctor_visits_1_idx` (`doctor_id` ASC),
183 CONSTRAINT `fk_doctor_visits_2`
184 FOREIGN KEY (`patient_id`)
185 REFERENCES `hospitaldb`.`patients` (`id`)
186 ON DELETE NO ACTION
187 ON UPDATE NO ACTION,
188 CONSTRAINT `fk_doctor_visits_1`
189 FOREIGN KEY (`doctor_id`)
190 REFERENCES `hospitaldb`.`doctors` (`id`)
191 ON DELETE NO ACTION
192 ON UPDATE NO ACTION)
193ENGINE = InnoDB;
194
195CREATE TABLE IF NOT EXISTS `hospitaldb`.`medicine_assignments` (
196 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
197 `medicine_id` INT UNSIGNED NULL,
198 `doctor_visit_id` INT UNSIGNED NULL,
199 `units` INT NULL,
200 `delivered_units` INT NULL,
201 PRIMARY KEY (`id`),
202 INDEX `fk_medicine_assignments_1_idx` (`medicine_id` ASC),
203 INDEX `fk_medicine_assignments_2_idx` (`doctor_visit_id` ASC),
204 CONSTRAINT `fk_medicine_assignments_1`
205 FOREIGN KEY (`medicine_id`)
206 REFERENCES `hospitaldb`.`medicines` (`id`)
207 ON DELETE NO ACTION
208 ON UPDATE NO ACTION,
209 CONSTRAINT `fk_medicine_assignments_2`
210 FOREIGN KEY (`doctor_visit_id`)
211 REFERENCES `hospitaldb`.`doctor_visits` (`id`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION)
214ENGINE = InnoDB;
215
216CREATE TABLE IF NOT EXISTS `hospitaldb`.`bed_assignments` (
217 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
218 `bed_id` INT UNSIGNED NULL,
219 `patient_id` INT UNSIGNED NULL,
220 `admission_id` INT UNSIGNED NULL,
221 `from_date` DATETIME NULL,
222 `to_date` DATETIME NULL,
223 PRIMARY KEY (`id`),
224 INDEX `fk_bed_assignments_1_idx` (`bed_id` ASC),
225 INDEX `fk_bed_assignments_2_idx` (`patient_id` ASC),
226 INDEX `fk_bed_assignments_3_idx` (`admission_id` ASC),
227 CONSTRAINT `fk_bed_assignments_1`
228 FOREIGN KEY (`bed_id`)
229 REFERENCES `hospitaldb`.`beds` (`id`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION,
232 CONSTRAINT `fk_bed_assignments_2`
233 FOREIGN KEY (`patient_id`)
234 REFERENCES `hospitaldb`.`patients` (`id`)
235 ON DELETE NO ACTION
236 ON UPDATE NO ACTION,
237 CONSTRAINT `fk_bed_assignments_3`
238 FOREIGN KEY (`admission_id`)
239 REFERENCES `hospitaldb`.`admission` (`id`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION)
242ENGINE = InnoDB;
243
244CREATE TABLE IF NOT EXISTS `hospitaldb`.`payments` (
245 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
246 `payment_amount` DECIMAL(10,2) NULL,
247 `payment_for` ENUM('medicine_assignment', 'investigation_assignment', 'bed_assignment', 'doctor_visit') NULL,
248 `payment_for_id` INT UNSIGNED NULL,
249 `payment_paid` TINYINT(1) NULL,
250 `payment_method` ENUM('cash', 'card', 'check') NULL,
251 `paid_at` DATETIME NULL,
252 `patient_id` INT UNSIGNED NULL,
253 PRIMARY KEY (`id`),
254 INDEX `fk_payments_1_idx` (`patient_id` ASC),
255 CONSTRAINT `fk_payments_1`
256 FOREIGN KEY (`patient_id`)
257 REFERENCES `hospitaldb`.`patients` (`id`)
258 ON DELETE NO ACTION
259 ON UPDATE NO ACTION)
260ENGINE = InnoDB;