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