COVID-19 ER Diagram

The Diagram

This week, we were tasked with creating an Entity relationship diagram concerning risk factors for COVID-19. Don't worry, I'll keep it simple.

(I hope that picture works because all I did was drag and drop.)

High level overview and keys

I apologize for the video. It's been a day. Enjoy this picture of an otter, because I need it, so maybe someone else does too.

Entities

Otherwise known as the what of the diagram, I have laid out seven entities. They are, as you can see above: Patient, Location, Hospitals, Job, Lifestyle, House, and Risk. That last one is the most important as it contains the final risk level of the patient. These seven entities encompass a patient's entire daily life. I will break it down further by entity, starting with the Patient.

Patient

The patient, the core of our diagram, has five main attributes, or characteristics of the entity, and a Universally Unique Identifier (UUID). Age, Height, and Weight are all basic data points that every medical professional needs. These data points help determine how your body will react to medications and how high of a dosage you need. The mask attribute refers to what kind of mask a person will wear, if any. Health Insurance is also needed as someone will worse health care, or none, is less likely to go see a doctor if they are actually sick. Each patient lives in a location. These two entities are connected by a relationship, or a verb that connects the two.

UUID, UID, and Cardinality

Now is a good time to point out the distinction between UUID and Unique ID (UID). Patient, Location, and Hospitals all have a UUID, while the rest of the entities have UIDs. In creating this diagram, I found that the attributes for Patient, Location, and Hospitals can either be shared within the database, or shared within the medical field. This is because of cardinality, or how many other entities can map to other entities. Patient to Location is a many to one relationship. One person is mapped to a Location, but that Location can have many people linked to it. The reverse goes for the relationship of Location to Hospitals; one location can have many Hospitals. The rest of the connections from Patient are all one to one as there is too much variability to be shared between patients. No two patients are going to live the exact same lifestyle, or have the exact same risk factors.

Location

Location has six attributes total. Universal Healthcare reflects if the Country has universal healthcare or not. The Population Density is important as a higher density leads to a higher risk of infection. A similar concept follows colleges. They are risky as with the push to return to school, colleges have a large number of students returning from across the globe. A Location's mask policy refers to how strict enforcement is within the population. For example, Australia is strict with their mask policy, handing out fines to people on the street and seizing computers of anti-masker protestors breaking the law. On the other side of the spectrum, the United States.

Hospitals

The characteristics attached to Hospitals all revolve around the hospital's ability to handle COVID-19 cases. Hence, number of ventilators and ICU capacity are needed to calculate a patient's risk. At the same time, the type of hospital is important. The level of care can vary based if the hospital is a research based hospital, a hospital with college student employees, both, or neither. The number of hospitals nearby reflects how much or little other hospitals can help if an influx of cases appears. Same with the support network of labs and pharmacies. More labs can shorten the return on COVID test results, giving patients an earlier warning.

Job

A patient's job is one of the largest risk factors. Exposure to other people, as outlined in the Exposure to coworkers and Exposure to public attributes. PPE also reflects how protected from possible infection a person is in their job. Cashiers are a bit more protected than a soccer referee, which is what these attributes hope to reflect. Supply Chain and Products are focused more on retail jobs, but they can be applicable for any occupation. Some food products have been found to contain COVID, and how the supply chain operates, autonomously or with people, can contribute to greater risk in the workplace. Ventilation within the workplace, and the number of coworkers, are there for Parts per Million, or how many COVID particles are in a space. The higher the density, the higher risk of becoming infected. More coworkers and poor ventilation lead to higher concentration, while better ventilation lowers the risk.

House

House has six attributes in total, mainly focused around the possible exposure at home. Exposure to other tenants is aimed towards apartment buildings, while roommate exposure is focused on the apartment itself. Social hub is in reference to how many people are in and out of the house that are not residences of the house. Number of roommates is another risk factor as you are closer to them than the people that come and go from your place. Ventilation is also key here, as poor ventilation increases the risk of exposure.

Lifestyle

A person's lifestyle is the biggest factor in recovery. All of the attributes that go into Lifestyle help determine a Patient's ability to fight off COVID, or any infection for that matter. Medical professionals are better at reading this data than I am, but for the most part, a better diet and more exercise decrease risk, while a large amount of smoking and drinking increase risk.

Risk

The final entity, Risk, is the most important in the diagram, as it contains the Patient's calculated risk level as an attribute. Risk also contains an attribute for all of the other IDs for reference. Exposure to the public here is different than in Job and House, as this is for public trips, such as running errands or going out. The rest of the attributes deal with basic medical information that relates directly to COVID risk. We can't store a patient's full medical history here as that is a security risk. Only the information needed to determine COVID infection risk, such as any conditions or surgeries on the lungs, is needed, and that would be encrypted in the final product.

Final note

That's it! A long winded, broken down explanation of my diagram. In the future, we will explore this diagram further, so keep an eye out for more blog posts. And if you would like to see more photos to brighten up your day, leave a like or a comment. It's a cold world out there, maybe I can brighten it up a bit in my very technical blog. :)