Modeling Relationships Between Tables for Normalization

Today we're diving into a super important topic: modeling relationships between tables. Normalization isn't just about atomic data and removing redundancy, it's also about creating the right relationships between tables.

If databases are an organized way to store info, then relationships between tables are like logical bridges that show how data interacts with each other. Imagine a library where info about books is stored separately from info about authors, but each book "knows" its author through a special relationship. Or take an online store: product data exists independently from customer info, but when someone places an order, the system links a specific customer to specific products through the orders table.

In a medical clinic, patients are linked to their medical records, doctors to their appointment schedules, and meds to prescriptions. These relationships help the system understand what info belongs to what, without duplicating data unnecessarily.

The main types of these relationships work just like real life: a passport belongs to only one person (one-to-one), one teacher can teach several courses (one-to-many), and students can enroll in different subjects, with each subject attended by different students (many-to-many).

One-to-One (1:1)

This is a relationship where one record in table "A" matches exactly one record in table "B". For example, we have tables "Employees" and "Passport Data". One employee can have only one passport, and each passport belongs to a single employee.

Example:

Employees

Passport Data

Here the relationship is made through the foreign key employee_id, which points to the id in the "Employees" table.

One-to-Many (1:N)

This is the most common type of relationship. Here, each record from table "A" can be linked to several records in table "B", but each record from table "B" is linked to only one record from table "A". For example, we have tables "Teachers" and "Courses". One teacher can teach several courses.

Example:

Teachers

Courses

The relationship is created through the foreign key teacher_id in the "Courses" table.

Many-to-Many (M:N)

When you have a ton of stuff, it's fun but tricky. Here, each record in table "A" can be linked to several records in table "B", and vice versa. For example, students can enroll in several courses, and each course can have several students.

Example:

Students

Courses

To link them, we need an intermediate table that stores the matches between students and courses:

Enrollments

Modeling Relationships Using Foreign Keys

A foreign key is a column (or set of columns) that points to a primary key column in another table. This is the foundation for building relationships between tables.

Example of a foreign key:

CREATE TABLE Courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

How do you avoid mistakes when designing foreign keys? First, make sure the data types between the foreign key and primary key columns match — otherwise the database just won't let you create the relationship. Also, think ahead about what should happen when you delete records. For example, if you delete a row from the parent table, what about the child records? One popular option is to use ON DELETE CASCADE, so related data gets deleted automatically with the main record. This helps keep things tidy and avoids "dangling" references.

Implementing a "Many-to-Many" Relationship

Let's take an example: we have students and courses. One student can be enrolled in several courses, and one course can have several students. To implement the M:N relationship, we'll create three tables: Students, Courses, and Enrollments.

CREATE TABLE Students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

Now we can add records to the Enrollments table to link students and courses.

Practical Assignment

Create a database structure for a course management system. You should have tables Students, Courses, and Enrollments. Implement all the relationships between the tables. Then add some sample data about students, courses, and their enrollments. Let's see how to do this.

1. Create the tables:

CREATE TABLE Students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

2. Insert data:

INSERT INTO Students (name) VALUES ('Otto Lin'), ('Maria Chi');
INSERT INTO Courses (name) VALUES ('SQL Basics'), ('DB Administration');
INSERT INTO Enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

3. Check the data:

SELECT
    Students.name AS student, 
    Courses.name AS course
FROM Enrollments
JOIN Students ON Enrollments.student_id = Students.id
JOIN Courses ON Enrollments.course_id = Courses.id;

Result:

Challenges and Features of Modeling Relationships

When you're modeling relationships between tables, you might run into issues like:

• Errors when deleting data (for example, you have records in a child table that depend on a record in the parent table).
• Query performance with a lot of data. M:N relationships are especially "hungry" since they need extra joins.

Here's how you can deal with these problems:

• Use indexes on foreign keys
• Think through your database structure.
• Balance normalization and performance.

We looked at modeling relationships between tables at a pretty basic level and tried it out by building a database structure for a course management system. I'd love to go through a big example, but honestly, I couldn't figure out how to do it. A big example just gets complicated and boring. And it's not really that useful. I'll try to get back to this closer to the end of the course.