Finding Relationships Between Entities

Hi friends!

In this part we will see how to find the relationships between the different entities we have already discovered. I suggest you check out the conversation and the entities once again before starting.

Just like we found nouns in the conversation to discover entities, we use verbs to find the relationships between entities. Verbs are action words — like "manage", "schedule" and "assign" — that show relationships between entities. Some of the verbs in our conversation:

• Manage — used with appointments, patient records, and staff.
• Schedule — describes managing appointments and the doctor's availability.
• Assign — refers to assigning staff to specific clinics.
• Track — relevant for monitoring appointment statuses or medicines prescribed.
• Record — used to document notes and patient information.
• Prescribe — links the doctor's prescriptions to specific medicines.
• Access — shows who can see or use specific information in the system.

Each of these verbs suggests a relationship between entities in our system. There are three types of relationships we may have.

One-to-One

When one entity (one row in a table) is related to only one row in another table, it is a one-to-one relationship. For example, consider the User–Doctor relation below: one doctor can only be linked to one user. Isn't it? Let's find the one-to-one relationships in our system.

• User – Doctor · define, associate
  Each doctor is a user with attributes related to medical practice. One doctor to one user.
• User – Patient · define, associate
  Each patient is a user with access to their appointments and records. One patient to one user.
• User – Staff · define, associate
  Each staff member is a user with permissions for managing appointments. One staff member to one user.
• Appointment – AppointmentNote · record, enter, link
  Each appointment has one set of notes that includes symptoms and advice. One appointment to one appointment note.

One-to-Many (or Many-to-One)

When one row in a table is related to many rows in another table, it is called a one-to-many relationship. Or, if many rows of one table are related to one row of another table, it is called many-to-one. So one-to-many and many-to-one are basically the same thing — it depends on how we look at it.

• User – Role · define, access
  Each user has one role that defines their permissions (e.g., Doctor, Staff, Patient). Many users share one role.
• Patient – Appointment · book, view, schedule
  A patient can have multiple appointments, but each appointment is for one patient. One patient to many appointments.
• Doctor – Appointment · manage, record
  A doctor manages multiple appointments, taking notes and prescribing medicines as needed. One doctor to many appointments.
• Appointment – Clinic · schedule, manage
  Each appointment happens in one clinic, but a clinic can host many appointments over time. Many appointments to one clinic.

Many-to-Many

When many rows of one table are related to many rows of another table, it is a many-to-many relationship. For example, consider the Staff–Clinic relationship below: many staff members (say, John, Mary, and so on) can work at many different clinics (Clinic X, Clinic Y, Clinic Z…). John may work in Clinic X and Clinic A, and Mary can work in Clinic Y and Clinic Z.

• Prescription – Medicine · prescribe, log
  Each prescription can include multiple medicines, and each medicine can appear in multiple prescriptions. Many prescriptions to many medicines.
• Staff – Clinic · assign, manage
  Each staff member can work at multiple clinics, and each clinic can have multiple staff members assigned. Many staff members work in many clinics.

In the next part, we will look at how to implement these relationships in our tables.