114 lines
2.9 KiB
Markdown
114 lines
2.9 KiB
Markdown
|
# Schema Design
|
||
|
|
|
||
|
|
## MySQL Database Design
|
||
|
|
|
||
|
|
### Table: patients
|
||
|
|
|
||
|
|
_Patient information_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- user_id: INT, Foreign Key → user(id)
|
||
|
|
- age: INT, Not Null, Constraint(age > 0)
|
||
|
|
- description: VARCHAR
|
||
|
|
- phone_number: VARCHAR -- Validated via Code
|
||
|
|
- email_address: VARCHAR -- Validated via Code
|
||
|
|
- home_address: VARCHAR
|
||
|
|
- archived: BOOLEAN, Not Null, default False -- When patient is removed it is marked as Archived and his active
|
||
|
|
appointments are marked as Cancelled.
|
||
|
|
|
||
|
|
### Table: appointments
|
||
|
|
|
||
|
|
_Appointment information_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- doctor_id: INT, Foreign Key → doctors(id)
|
||
|
|
- patient_id: INT, Foreign Key → patients(id)
|
||
|
|
- appointment_time: DATETIME, Not Null
|
||
|
|
- status: INT (0 = Scheduled, 1 = Completed, 2 = Cancelled)
|
||
|
|
- status_changed_timestamp: DATETIME, Not Null
|
||
|
|
- reason: VARCHAR, Not Null
|
||
|
|
|
||
|
|
### Table: doctors
|
||
|
|
|
||
|
|
_Doctor information_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- user_id: INT, Foreign Key → user(id)
|
||
|
|
- specialization: VARCHAR, Not Null
|
||
|
|
- phone_number: VARCHAR -- Validated via Code
|
||
|
|
- email_address: VARCHAR -- Validated via Code
|
||
|
|
- archived: BOOLEAN, Not Null, default False -- When doctor is removed it is marked as Archived and his active
|
||
|
|
appointments are marked as Cancelled.
|
||
|
|
|
||
|
|
### Table: user
|
||
|
|
|
||
|
|
_Basic user information for all system users_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- username: VARCHAR, Not Null, Unique
|
||
|
|
- password: CHAR(64) -- SHA256 hashed password value
|
||
|
|
- role: INT (0 = Admin, 1 = Doctor, 2 = Patient)
|
||
|
|
- name: VARCHAR, Not Null
|
||
|
|
- last_login_timestamp: DATETIME
|
||
|
|
- registration_timestamp: DATETIME, Not Null
|
||
|
|
|
||
|
|
### Table: office_hours
|
||
|
|
|
||
|
|
_Table storing regular office hours for a doctor_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- doctor_id: Foreign Key → doctors(id)
|
||
|
|
- day: INT (0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday), Not Null
|
||
|
|
- start: TIME, Not Null
|
||
|
|
- end: TIME, Not Null
|
||
|
|
- Constraint: start < end
|
||
|
|
|
||
|
|
### Table: unavailability_schedule
|
||
|
|
|
||
|
|
_Table storing temporary unavailability for a doctor_
|
||
|
|
|
||
|
|
- id: INT, Primary Key, Auto Increment
|
||
|
|
- doctor_id: Foreign Key → doctors(id)
|
||
|
|
- start: TIMETIME, Not Null
|
||
|
|
- end: TIMETIME, Not Null
|
||
|
|
- Constraint: start < end
|
||
|
|
|
||
|
|
## MongoDB Collection Design
|
||
|
|
|
||
|
|
### Collection: prescriptions
|
||
|
|
|
||
|
|
_Collection describing a prescriptions for the patients_
|
||
|
|
|
||
|
|
```json
|
||
|
|
{
|
||
|
|
"_id": "ObjectId('64abc123456')",
|
||
|
|
"doctorId":222222,
|
||
|
|
"patientId": 123213,
|
||
|
|
"appointmentId": 51,
|
||
|
|
"medication": "Paracetamol",
|
||
|
|
"dosage": "500mg",
|
||
|
|
"doctorNotes": "Take 1 tablet every 6 hours.",
|
||
|
|
"refillCount": 2,
|
||
|
|
"pharmacy": {
|
||
|
|
"name": "Walgreens SF",
|
||
|
|
"location": "Market Street"
|
||
|
|
}
|
||
|
|
"timestamp": "2011-12-03T10:15:30Z",
|
||
|
|
"expires:" "2012-12-03T10:15:30Z"
|
||
|
|
}
|
||
|
|
```
|
||
|
|
|
||
|
|
### Collection: messages
|
||
|
|
|
||
|
|
_Chat message between Doctor and Patient_
|
||
|
|
|
||
|
|
```json
|
||
|
|
{
|
||
|
|
"_id": "ObjectId('64abc123456')",
|
||
|
|
"_parentId": "ObjectId('64abc123456')",
|
||
|
|
"fromUserId": 121,
|
||
|
|
"toUserId": 121,
|
||
|
|
"message": "Where can I get the medicine from",
|
||
|
|
"timestamp": "2011-12-03T10:15:30Z"
|
||
|
|
}
|
||
|
|
```
|