Entity–Relationship Model

In relational databases like Postgres, a relationship between entities, i.e., tables, is implemented by storing the primary key of one entity as a pointer or "foreign key" in the table of another entity.

One-to-one

A one to one relationship between two entities occurs when a single record in one entity is related to only one record in another entity, and vice versa.

-- Already created by Keycloak
CREATE TABLE IF NOT EXISTS auth.user_entity (
    id VARCHAR(36) PRIMARY KEY
    -- other fields
);

CREATE TABLE IF NOT EXISTS user_profiles (
    id VARCHAR(36) PRIMARY KEY REFERENCES auth.user_entity(id)
);

One-to-many

A one to many relationship is where a single record in one entity can be associated with one or more records in another entity. This is the most common relationship type. In the parent table, use a primary key, id which is refernced using a foreign key, author_id, in the child table.

CREATE TABLE authors (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) PRIMARY KEY,
    -- first_name TEXT NOT NULL,
    -- last_name TEXT NOT NULL
);

CREATE TABLE books (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10001) PRIMARY KEY,
    -- title TEXT NOT NULL,
    author_id INT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
);

Many-to-many

A many to many relationship occurs when multiple records in one entity are related to multiple records in another entity. This type of relationship requires a junction table to manage the associations between the two entities. Create a third table (junction table) that holds foreign keys that reference the primary keys in the two entities being linked. This junction table effectively breaks down the many to many relationship into two 1 to many relationships.

CREATE TABLE students (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 100001) PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id INT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);