Hi friends!
In this part we will see how we can find the relationships between different entities we have already discovered. I suggest that you check out the conversation and entities once again before starting this part.
Just like we found nouns in the conversation to discover entities, we use verbs to find out 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 are:
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 (or one row in a table) is related to only one row in another table, it is one-to-one relationship. For an example, consider User - Doctor relation below, one doctor can only have relation to one user. Isn't it? Let's find out some of the one to one relationships in our system.
User - Doctor:
Verb: Define, Associate
Relationship: Each doctor is a user with attributes related to medical practice.
Type: One-to-One (One doctor to one user)
User - Patient:
Verb: Define, Associate
Relationship: Each patient is a user with access to their appointments and records.
Type: One-to-One (One patient to one user)
User - Staff:
Verb: Define, Associate
Relationship: Each staff member is a user with permissions for managing appointments.
Type: One-to-One (One staff to one user)
Appointment - AppointmentNote:
Verb: Record, Enter, Link
Relationship: Each appointment has one set of notes that includes symptoms and advice.
Type: One-to-One (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 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 relationship. So one-to-many and many-to-one are basically the same thing, depending on how we look at it.
Example 1: Consider the Patient-Appointment relationship below. One patient can have many appointments. Right?
Example 2: Consider the Appointment-Clinic relationship below. Many appointments can happen in one clinic. In other words, we can say that a clinic can have many appointments, and we would call it Clinic - Appointment instead. Okay?
User - Role:
Verb: Define, Access
Relationship: Each user has one role that defines their permissions (e.g., Doctor, Staff, Patient).
Type: Many-to-One (Many users share one role)
Patient - Appointment:
Verb: Book, View, Schedule
Relationship: A patient can have multiple appointments, but each appointment is for one patient.
Type: One-to-Many (One patient to many appointments)
Doctor - Appointment:
Verb: Manage, Record
Relationship: A doctor manages multiple appointments, taking notes and prescribing medicines as needed.
Type: One-to-Many (One doctor to many appointments)
Appointment - Clinic:
Verb: Schedule, Manage
Relationship: Each appointment happens in one clinic, but a clinic can host many appointments over time.
Type: Many-to-One (Many appointments to one clinic)
Many-to-Many
When many rows of one table are related to many rows of another table, it is many-to-many relationship. For example, consider 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, Clinic A, and so on). John may work in Clinic X and Clinic A, and Mary can work in Clinic Y and Clinic Z.
Prescription - Medicine:
Verb: Prescribe, Log
Relationship: Each prescription can include multiple medicines, and each medicine can appear in multiple prescriptions.
Type: Many-to-Many (Many prescriptions to many medicines)
Staff - Clinic:
Verb: Assign, Manage
Relationship: Each staff member can work at multiple clinics, and each clinic can have multiple staff members assigned.
Type: Many-to-Many (Many staff members can work in many clinics)
In the next part, we will look at how to implement these relationships in our tables.
Comments