Creating relations between tables PHPMYADMIN

Question

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:

1. Time should be linked to both Patients and Nurse
2. Priority should link to both Patients and Nurse
3. 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.

Answer 1

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)