Database Creation : Relationship Mapping
Part 2: Conceptual Model to a Relational Schema
Building on our exploration of converting requirements files to ER diagrams, this sequel dives into transforming those diagrams into relational models. We'll explore how to capture the relationships between entities and translate them into a well-structured relational schema.
Tips and Tricks on the General Approach
When translating an ER diagram into a relational model, consider these key factors:
Type of relationship: Identify the relationships between entities (one-to-one, one-to-many, many-to-many).
Participation: Determine how mandatory the participation is for each entity in the relationship (optional or mandatory on one or both sides).
Understanding Key Concepts:
To effectively capture these relationships, we need to understand two important database concepts:
Primary Key: A unique identifier for each row (record) in a table. It acts as the central reference point for that specific record.
Foreign Key: A column in one table that references the primary key of another table. This establishes a link between related tables.
A "rule of thumb" is as follows;
The Hospital Database Implementation:
In the previous part of this blog series, we explored how to translate requirements into a conceptual ER diagram. Now, we'll delve into transforming those relationships into a well-structured relational model for our hospital database. This involves understanding how different entity relationships translate into table structures and foreign keys.
Refresher on Cardinality and Participation
Before diving into specific examples, let's establish two key concepts that govern relationships between tables:
Cardinality: This describes the number of occurrences in one table in relation to another. Common cardinalities include:
One-to-One: A single record in one table relates to exactly one record in another table.
One-to-Many: A single record in one table relates to many records in another table.
Many-to-Many: Many records in one table relate to many records in another table.
Participation: This defines whether an entity's participation in a relationship is mandatory or optional. It can be mandatory (all records must participate) or optional (some records may not participate).
Translating Relationships into Tables
Let's use our hospital database example to see how these concepts translate into relational models:
One-to-Many Relationships:
In a one-to-many relationship, the primary key of the "one" side (parent table) becomes a foreign key in the "many" side (child table). Here are some examples from our hospital database:
Personnel - Departments: Each staff member belongs to one department, and a department can have many staff members (One-to-Many). The Department ID (primary key of Departments) becomes a Foreign key in the Personnel table.
Services - Departments: A department offers many services, but a service belongs to only one department (One-to-Many). The Department ID (primary key of Departments) becomes a Foreign key in the Services table.
Medications - Pharmacy: Each medication is associated with one pharmacy, but a pharmacy can have many medications (One-to-Many). The Pharmacy ID (primary key of Pharmacy) becomes a Foreign key in the Medications table.
Inpatients - Nurses: Each inpatient is assigned to one nurse at a time, while a nurse can care for multiple inpatients (One-to-Many). The Nurse ID (primary key of Nurses) becomes a Foreign key in the Inpatients table.
Inpatients - Rooms: A room can house multiple inpatients over time, but each inpatient is allocated to one room at a given time (One-to-Many). The Room ID (primary key of Rooms) becomes a Foreign key in the Inpatients table.
Many-to-Many Relationships:
Many-to-many relationships require an associative table to link the two main tables. This associative table includes the primary keys from both original tables. Here are some examples:
Patients - Physicians: A patient can see multiple physicians, and a physician can have many patients (Many-to-Many). We create an associative table, Appointments, with foreign keys for both Patient ID and Physician ID.
Patients - Medications: A patient can receive multiple medications, and a medication can be prescribed to many patients (Many-to-Many). We create an associative table, Prescriptions, with foreign keys for both Patient ID and Medication ID.
Lab Tests - Patients: A patient can undergo multiple lab tests, and a lab test can be performed on multiple patients (Many-to-Many). We create an associative table, Test Ordered, with foreign keys for both lab Test ID and Patient ID.
One-to-One Relationships (Optional):
Inpatients - HMIS and Inpatients - Bill: We've chosen to model these relationships as one-to-one for our hospital database. This means a single inpatient has one corresponding HMIS record and one corresponding bill. We'll establish these relationships using foreign keys:
The primary key of the Inpatients table will be a foreign key in the HMIS table.
The primary key of the Inpatients table will be a foreign key in the Bills table.
Important Consideration:
It's worth noting that depending on the specific functionalities of HMIS and Bills, it might be more efficient to combine them with the Inpatients table to avoid data redundancy. This is a design decision that should be made based on your specific data model and querying needs.
As we wrap up this installment of our blog series, we've laid the groundwork for transitioning from an ER diagram to a relational model. By understanding cardinality, participation, and how to implement them using foreign keys and associative tables, you can establish a solid foundation for your database design.
In the next part of this series, we'll delve deeper into the practical aspects of building your database. We'll explore how to define appropriate data types, set constraints to ensure data integrity, and explore the concept of database normalization to optimize your schema design.
Hopefully, by the end of this we're all one step closer to building better robust databases.
Catch you in the next one!
Refresher on Part 1 of the series:
Link to Blog Post 1: Database Creation From Scratch: Part 1 : From a Requirements file to a Conceptual Model (https://datacurious.hashnode.dev/database-creation-from-scratch)