Creating Relationships Using Foreign Keys and Junction Tables

How do we establish relationships between our entities, or tables? Let's see how to create relationships using foreign keys and junction tables.

We have already discussed that primary keys are the attributes (columns) in each table that uniquely identify a record. Foreign keys that we create in a table are always the primary key of some other table.

In this part, we'll go through each entity in our system, discussing where to add foreign keys and how to set up junction tables when needed. Let's dive in.

User and Role

Relationship: each User has a Role that defines permissions (e.g., Doctor, Staff, Patient).
Foreign key: add RoleId in the User table to reference the Role table's Id column.

So the resulting User table is:

User and Doctor, Patient, Staff

Relationship: Doctor, Patient, and Staff are specific types of Users.
Foreign key: add UserId in each of the Doctor, Patient, and Staff tables to reference the User table. This makes each doctor, patient, or staff member uniquely identifiable as a user.

Patient and Appointment

Relationship: each Appointment is linked to one Patient, but a patient can have multiple appointments.
Foreign key: add PatientId in the Appointment table to reference the Patient table.

Doctor and Appointment

Relationship: each Appointment is managed by one Doctor, and each doctor can have multiple appointments.
Foreign key: add DoctorId in the Appointment table to reference the Doctor table.

Clinic and Appointment

Relationship: each Appointment takes place at a Clinic, but each clinic can host multiple appointments.
Foreign key: add ClinicId in the Appointment table to reference the Clinic table.

Appointment and AppointmentNote

Relationship: each Appointment can have one AppointmentNote associated with it, recording details like symptoms and advice.
Foreign key: add AppointmentId in the AppointmentNote table to reference the Appointment table.

AppointmentNote and Prescription

Relationship: each AppointmentNote can have multiple Prescriptions associated with it.
Foreign key: add AppointmentNoteId in the Prescription table to reference the AppointmentNote table.

Doctor and Availability

Relationship: Availability connects a Doctor with specific clinics and times. Each entry records when a doctor is available at a clinic.
Foreign keys: add DoctorId and ClinicId in the Availability table, referencing the Doctor and Clinic tables.

Now let's see some many-to-many relationships.

Prescription and Medicine (junction table)

Relationship: each Prescription can contain multiple Medicines, and each medicine can appear in multiple prescriptions. This is a many-to-many relationship, so we create a junction table to handle it.
Junction table: a new table called PrescriptionMedicine with foreign keys PrescriptionId and MedicineId.

Staff and Clinic (junction table)

Relationship: each Staff member can work in multiple Clinics, and each clinic can have multiple staff members. Another many-to-many relationship, another junction table.
Junction table: a new table called StaffClinic with foreign keys StaffId and ClinicId.

The full picture

Here is the design with only the keys — Primary Keys (PK) and Foreign Keys (FK) — so you can see every connection at a glance:

By creating foreign keys and junction tables, we establish clear connections between the different parts of the database. Foreign keys create one-to-many and one-to-one relationships, while junction tables handle many-to-many relationships — ensuring that data across tables is properly linked.