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:

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.

What are your table structures? – Chris Payne Jan 30 '13 at 21:41 — Chris Payne, Jan 30 '13 at 21:41
I have edited the question with the table structures – user2027028 Jan 30 '13 at 22:02 — user2027028, Jan 30 '13 at 22:02

score 0 · Answer 1 · answered Jan 31 '13 at 00:05

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)

Perhaps you have an email address I can explain this better? — user2027028, Jan 31 '13 at 18:38
That defeats the purpose of StackOverflow - letting many people learn from and contribute to these questions and answers. If my answer is not at all helpful to you, let me know and I'll delete it. — Benny Hill, Jan 31 '13 at 18:44
Your answer was useful, Im having difficulty determining a relationship between each table. Tables I have: PATIENTS(id, forename, surname, gender, dob, address, patient history, priority, timeid) — user2027028, Jan 31 '13 at 18:47
In my example anywhere you see "F" it indicates a foreign key and then the table and field name it points to. For example, (F visits.id) would be a foreign key to the "id" field in the "visits" table. Is there a particular relationship in my example that is missing? Or do I have relationships defined that don't make sense to you? — Benny Hill, Jan 31 '13 at 18:56
how would i relate NURSE assesses CONDITION of PATIENT? And time(id, arrival time, expected waiting time, discharge) is related to nurse because she gives the patient a waiting time based on their condition and priority and also relates to patients. Same with prioirty; linked to both patient and nurse because the nurse inputs the patients prioirty and the patient is put into a priority based on their condition. 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. — user2027028, Jan 31 '13 at 19:13
is there any way i can post an image of the tables on here? It would explain the strucutre a lot better — user2027028, Jan 31 '13 at 19:23
i am unable to chat because I need reputation points or something. Is sending you my email address acceptable? — user2027028, Jan 31 '13 at 19:30
You can use something like imageshack.us to upload an image if you think that would help. — Benny Hill, Jan 31 '13 at 19:34
okay, what is the best method in phpmyadmin to create relationships between the tables? Should i create foreign contrainsts for each table or create the relations in designer view? — user2027028, Jan 31 '13 at 19:53
I want to insert values from the form into multiple tables. For example; "priority (high, moderate, low) into the Patients table AND priority table. I have the values inserting into the Patients table fine using php, how would I insert the values into BOTH patient and Priority? — user2027028, Feb 02 '13 at 12:42
It would be helpful if you could edit your question and show ALL the tables and ALL the columns you think you need. Also - what does A&E stand for? — Benny Hill, Feb 02 '13 at 13:46
here is a clear description of what I am expected to achieve. I hope this is a lot clearer and A&E stands for Accident and Emergency - Emergency Department in the states? http://stackoverflow.com/questions/14663031/php-form-submission-for-mysql-phpmyadmin — user2027028, Feb 02 '13 at 14:44
I have explained it better here http://stackoverflow.com/questions/14664368/insert-a-value-into-multiple-tables/14664872#comment20514308_14664872 — user2027028, Feb 05 '13 at 12:58

Creating relations between tables PHPMYADMIN

1 Answers1

Linked