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