· 9 years ago · Nov 16, 2016, 04:42 PM
1CREATE TABLE IF NOT EXISTS `user`
2(
3 `id` int NOT NULL AUTO_INCREMENT,
4 `username` char(64) NOT NULL,
5 `password` char(64) NOT NULL,
6 `name` char(128) NOT NULL,
7 `email` char(255) NOT NULL,
8 `DOB` date NOT NULL,
9 `gender` boolean NOT NULL,
10 `married` boolean DEFAULT NULL,
11 `phone` int(16) DEFAULT NULL,
12 `joindate` date DEFAULT NULL,
13 `visibility` int DEFAULT NULL,
14 `type` int NOT NULL,
15 `status` tinyint DEFAULT NULL,
16 CONSTRAINT `pk_user` PRIMARY KEY (`id`),
17 CONSTRAINT `uk1_user` UNIQUE KEY (`username`),
18 CONSTRAINT `uk2_user` UNIQUE KEY (`email`)
19);
20
21CREATE TABLE IF NOT EXISTS `user_address`
22(
23 `id` int NOT NULL AUTO_INCREMENT,
24 `user_id` int NOT NULL,
25 `street` varchar(255) NOT NULL,
26 `city` char(64) NOT NULL,
27 `state` char(64) NOT NULL,
28 `country` char(64) NOT NULL,
29 CONSTRAINT `pk_address` PRIMARY KEY (`id`),
30 CONSTRAINT `fk1_address` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
31);
32
33CREATE TABLE IF NOT EXISTS `case_symptom`
34(
35 `case_id` int NOT NULL,
36 `symptom_id` int NOT NULL,
37 CONSTRAINT `pk_case_symptom` PRIMARY KEY (`case_id`, `symptom_id`),
38 CONSTRAINT `fk1_case_symptom` FOREIGN KEY (`case_id`) REFERENCES `case` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
39);
40
41CREATE TABLE IF NOT EXISTS `case_solution`
42(
43 `case_id` int NOT NULL,
44 `doctor_id` int NOT NULL,
45 `issue_id` int NOT NULL,
46 `solution` text NOT NULL,
47 CONSTRAINT `pk_case_solution` PRIMARY KEY (`case_id`, `doctor_id`),
48 CONSTRAINT `fk1_case_solution` FOREIGN KEY (`case_id`) REFERENCES `case` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
49 CONSTRAINT `fk2_case_solution` FOREIGN KEY (`doctor_id`) REFERENCES `user_doctor` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
50);
51
52CREATE TABLE IF NOT EXISTS `case`
53(
54 `id` int NOT NULL AUTO_INCREMENT,
55 `patient_id` int NOT NULL,
56 `name` char(128) NOT NULL,
57 `start_date` date NOT NULL,
58 `end_date` date DEFAULT NULL,
59 `description` text NOT NULL,
60 `photo` mediumblob,
61 `bestsolution_doctor_id` int DEFAULT NULL,
62 CONSTRAINT `pk_case` PRIMARY KEY (`id`),
63 CONSTRAINT `fk1_case` FOREIGN KEY (`patient_id`) REFERENCES `user_patient` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
64 CONSTRAINT `fk2_case` FOREIGN KEY (`bestsolution_doctor_id`) REFERENCES `user_doctor` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
65);
66
67CREATE TABLE IF NOT EXISTS `user_patient`
68(
69 `user_id` int NOT NULL,
70 `bloodgroup` tinyint DEFAULT NULL,
71 `totalcases` int DEFAULT NULL,
72 `visibility` int DEFAULT NULL,
73 CONSTRAINT `pk_patient` PRIMARY KEY (`user_id`),
74 CONSTRAINT `fk1_patient` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
75);
76
77CREATE TABLE IF NOT EXISTS `user_doctor`
78(
79 `user_id` int NOT NULL,
80 `bestcases` int DEFAULT NULL,
81 `solvedcases` int DEFAULT NULL,
82 `visibility` int DEFAULT NULL,
83 CONSTRAINT `pk_doctor` PRIMARY KEY (`user_id`),
84 CONSTRAINT `fk1_doctor` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
85);
86
87CREATE TABLE IF NOT EXISTS `doctor_education`
88(
89 `id` int NOT NULL AUTO_INCREMENT,
90 `doctor_id` int NOT NULL,
91 `degree` char(128) NOT NULL,
92 `field` char(128) NOT NULL,
93 `institute` char(128) NOT NULL,
94 `location` char(255) NOT NULL,
95 `start_year` int(4) NOT NULL,
96 `end_year` int(4) DEFAULT NULL,
97 CONSTRAINT `pk_education` PRIMARY KEY (`id`),
98 CONSTRAINT `fk1_education` FOREIGN KEY (`doctor_id`) REFERENCES `user_doctor` (`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE
99);
100
101CREATE TABLE IF NOT EXISTS `doctor_experience`
102(
103 `id` int NOT NULL AUTO_INCREMENT,
104 `doctor_id` int NOT NULL,
105 `jobtitle` char(128) NOT NULL,
106 `type` tinyint NOT NULL,
107 `company` char(128) NOT NULL,
108 `location` char(255) NOT NULL,
109 `start_year` int(4) NOT NULL,
110 `end_year` int(4) DEFAULT NULL,
111 CONSTRAINT `pk_experience` PRIMARY KEY (`id`),
112 CONSTRAINT `fk1_experience` FOREIGN KEY (`doctor_id`) REFERENCES `user_doctor` (`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE
113);
114
115CREATE TABLE IF NOT EXISTS `route`
116(
117 `id` int NOT NULL AUTO_INCREMENT,
118 `name` char(128) NOT NULL,
119 `p_id` int DEFAULT NULL,
120 `level` int DEFAULT 1,
121 CONSTRAINT `pk_route` PRIMARY KEY (`id`),
122 CONSTRAINT `fk1_route` FOREIGN KEY (`p_id`) REFERENCES `route` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
123);
124
125CREATE TABLE IF NOT EXISTS `route_issue`
126(
127 `route_id` int NOT NULL,
128 `issue_id` int NOT NULL,
129 `count` int DEFAULT NULL,
130 CONSTRAINT `pk_route_issue` PRIMARY KEY (`route_id`, `issue_id`),
131 CONSTRAINT `fk1_route_issue` FOREIGN KEY (`route_id`) REFERENCES `route` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
132);
133
134CREATE TABLE IF NOT EXISTS `route_symptom`
135(
136 `route_id` int NOT NULL,
137 `symptom_id` int NOT NULL,
138 `count` int DEFAULT NULL,
139 CONSTRAINT `pk_route_symptom` PRIMARY KEY (`route_id`, `symptom_id`),
140 CONSTRAINT `fk1_route_symptom` FOREIGN KEY (`route_id`) REFERENCES `route` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
141);