Please help me! Im creating a database to store data about patients in an A&E Department. However, Im unsure how to relate the tables.
Table structure:
PATIENTS (PatientID(PK),Forename, surname, gender, DOB, Address, History, illness, priority)
A&E (ID(PK), PatientID(FK), address, city, postcode, telenumber)
NURSE (NurseID(PK), forename, surname)
CONDITION (ID(PK), PatientID(FK) symptoms, diagnosis, treatment)
Basically the relationships between these are:
PATIENT attends A&E
PATIENT seen_by NURSE
NURSE assesses CONDITION of PATIENT
Here are the relations that need to be added:
- Time should be linked to both Patients and Nurse
- Priority should link to both Patients and Nurse
- Doctors should be linked to time and priority (doctors will make a decision based on waiting time and priority of patient). There is no direct link between doctor and patients.
This is imperitive that I get these relations correct so any help would be greatly appreciated. Thankyou.
Your question is not at all clear to me. For instance you talk about patients and nurses being linked to "Priority" and doctors being linked to "Time" and "Priority" - and you write as if these are other tables - but you don't describe them.
Below is how I would solve your problem, based on the foggy understanding I have of your problem.
I don't know what A&E means. I'm assuming it's a clinic, hospital or other facility.
aes
id unsigned int(P)
street_address varchar(75)
city_id unsigned int(F cities.id)
postcode varchar(10) // Whatever the size of your postal code.
telenumber varchar(10) // Whatever the size of your telenumber.
Obviously your "state" could be different. Here in the United States our states all have a unique 2-character code.
cities
id unsigned int(P)
name varchar(50)
state_id char(2)(F states.id)
You didn't describe what you need to know about doctors so I'm assuming it's the same as what you need to know about nurses.
doctors
id unsigned int(P)
forename varchar(50)
surname varchar(50)
Things like: influenza, bronchitis, sinus infection, etc.
illnesses
id unsigned int(P)
description varchar(75)
nurses
id unsigned int(P)
forename varchar(50)
surname varchar(50)
I put the patient history into it's own table so we can associate multiple illnesses with each patient as well as a date for when the patient had each illness.
patient_history
id unsigned int(P)
patient_id unsigned int(F patients.id)
illness_id unsigned int(F illnesses.id)
qwhen date
patients
id unsigned int(P)
forename varchar(50)
surname varchar(50)
gender enum('f','m')
dob date
street_address varchar(75)
city_id unsigned int(F cities.id)
postcode varchar(10) // Whatever the size of your postal code.
telenumber varchar(10) // Whatever the size of your telenumber.
Again, your "states" may have a different size id or name.
states
id char(2)(P)
name varchar(50)
Things like: dizziness, fatigue, sinus congestion, shortness of breath, etc.
symptoms
id unsigned int(P)
description varchar(50)
This table holds information about every time a patient visits an A&E. Any record that doesn't have an end_time would represent a patient who is currently at an A&E waiting to be seen by a doctor/nurse. You can determine how long a patient has been waiting by comparing the current time to the beg_time. And of course priority would be entered/updated by the admitting clerk, nurse, doctor, etc.
visits
id unsigned int(P)
patient_id unsigned int(F patients.id)
nurse_id unsigned int(F nurses.id)
ae_id unsigned int(F aes.id)
priority unsigned tinyint // 1 = Critical, 2 = Urgent, 3 = whatever...
beg_time datetime
end_time datetime
Multiple doctors might see a patient...
visits_doctors
id unsigned int(P)
visit_id unsigned int(F visits.id)
doctor_id unsigned int(F doctors.id)
And multiple nurses might see a patient...
visits_nurses
id unsigned int(P)
visit_id unsigned int(F visits.id)
nurse_id unsigned int(F nurses.id)
A patient usually has multiple symptoms when they come in for a visit...
visits_symptoms
id unsigned int(P)
visit_id unsigned int(F visits.id)
symptom_id unsigned int(F symptoms.id)
