Data Modelling - Relationships in PostgreSQL

Introduction

In relational databases, relationships between tables are a fundamental aspect of data modeling. PostgreSQL supports various types of relationships including one-to-one, one-to-many, and many-to-many. Understanding these relationships is crucial for designing efficient and scalable databases.

One-to-One Relationship

In a one-to-one relationship, each row in one table is linked to a single row in another table. This can be implemented using a foreign key with a unique constraint. Here’s an example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INT UNIQUE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users (id)
);
                    

CREATE TABLE
CREATE TABLE
                    

In this example, each user has a unique profile.

One-to-Many Relationship

In a one-to-many relationship, a single row in one table can be linked to multiple rows in another table. This is achieved using a foreign key. Here’s an example:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors (id)
);
                    

CREATE TABLE
CREATE TABLE
                    

In this example, each author can have multiple books.

Many-to-Many Relationship

In a many-to-many relationship, multiple rows in one table can be related to multiple rows in another table. This is typically implemented using a junction table. Here’s an example:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id) REFERENCES courses (id)
);
                    

CREATE TABLE
CREATE TABLE
CREATE TABLE
                    

In this example, each student can enroll in multiple courses, and each course can have multiple students.

Querying Relationships

To retrieve data from related tables, you can use JOIN operations. Here’s an example query to get the books and their authors:

SELECT books.title, authors.name
FROM books
JOIN authors ON books.author_id = authors.id;
                    

 title      | name      
------------+-----------
 The Hobbit | J.R.R. Tolkien
 Dune       | Frank Herbert
                    

This query joins the books and authors tables to retrieve book titles along with author names.

Deleting Relationships

When deleting rows from a table, you need to consider the impact on related rows. PostgreSQL supports different actions such as CASCADE and RESTRICT. Here’s an example:

DELETE FROM authors WHERE id = 1 CASCADE;
                    

DELETE 1
                    

This command deletes an author and all related books. The CASCADE option ensures that related rows in the books table are also deleted.