在表之间创建关系PHPMYADMIN [英] Creating relations between tables PHPMYADMIN

问题描述

请帮助我！我在急诊室创建了一个数据库来存储有关患者的数据.但是，我不确定如何关联表格.

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.

表结构:

患者(患者ID(PK)，姓氏，姓氏，性别，DOB，地址，病史，疾病，优先级)

PATIENTS (PatientID(PK),Forename, surname, gender, DOB, Address, History, illness, priority)

A& E(ID(PK)，PatientID(FK)，地址，城市，邮政编码，电话号码)

A&E (ID(PK), PatientID(FK), address, city, postcode, telenumber)

NURSE(NurseID(PK)，姓氏，姓氏)

NURSE (NurseID(PK), forename, surname)

状态(ID(PK)，PatientID(FK)症状，诊断，治疗)

CONDITION (ID(PK), PatientID(FK) symptoms, diagnosis, treatment)

这些之间的关系基本上是:

Basically the relationships between these are:

患者参加A& E

PATIENT attends A&E

NURSE看过的患者

PATIENT seen_by NURSE

护士评估患者的状况

以下是需要添加的关系:

Here are the relations that need to be added:

1. 时间应该与患者和护士都相关
2. 优先级应同时与患者和护士联系
3. 医生应链接到时间和优先级(医生将根据患者的等待时间和优先级做出决定).医生和患者之间没有直接联系.

这是当务之急，我必须正确处理这些关系，因此将不胜感激.谢谢你.

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.

我不知道A& E是什么意思.我假设它是诊所，医院或其他设施.

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.

很显然，您的状态"可能有所不同.在美国，我们各州都有唯一的2个字符的代码.

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.

同样，您的州"可能具有不同的尺寸ID或名称.

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)

此表保存有关患者每次就诊A& E的信息.没有end_time的任何记录都将代表当前正在A& E中等待医生/护士看诊的患者.您可以通过将当前时间与beg_time进行比较来确定患者等待了多长时间.当然，优先级将由招生秘书，护士，医生等输入/更新.

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)

这篇关于在表之间创建关系PHPMYADMIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！