How do we establish relationships between our entities or tables? Let's see how we can create relationships using foreign keys and junction tables in this section.
We have already discussed that primary keys are the attributes (or columns) in each table that uniquely identify a record. Foreign keys that we create in a table are always primary key of some other table.
The relationships are established using foreign keys and sometimes junction tables for more complex, many-to-many connections. Foreign keys allow us to connect one table to another, like linking an appointment to the patient who booked it.
Junction tables act as bridges between two tables when there are many-to-many relationships, like connecting multiple staff members to multiple clinics.
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 into it.
User and Role
Relationship: Each User has a Role that defines permissions (e.g., Doctor, Staff, Patient)
Foreign Key: Add RoleId as a foreign key in the User table to reference the Role table (Role table's Id column).
So, the resultant User table is:
PK | Id |
|---|---|
FK | RoleId |
Username | |
Password | |
Name | |
ContactNo | |
User and Doctor, Patient, Staff
Relationship: Doctor, Patient, and Staff are specific types of Users.
Foreign Key: Add UserId as a foreign key in each of 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 as a foreign key 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 as a foreign key 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 as a foreign key 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 as a foreign key 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 as a foreign key in the Prescription table to reference the AppointmentNote table.
Doctor and Availability
Relationship: Availability connects Doctor with specific clinics and times. Each entry records when a doctor is available at a clinic.
Foreign Keys:
Add DoctorId as a foreign key in the Availability table to reference the Doctor table.
Add ClinicId as a foreign key in the Availability table to reference the Clinic table.
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: We add 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. This is another many-to-many relationship, so we create a junction table.
Junction Table: A new table called StaffClinic with foreign keys StaffId and ClinicId.
You can see and illustration with only the keys - Primary Keys (PK) and Foreign Keys (FK) in the illustration on top of this page.
By creating foreign keys and junction tables, we establish clear connections between different parts of the database. Foreign keys help 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.
Comments