Relationship Mapping 1n - Learning Module

Loading content...

0/241

Foreign Key on N-Side

The Universal Mapping Strategy

When transforming a one-to-many relationship from an ER diagram to relational tables, there is one dominant, nearly universal approach: place a foreign key column in the table representing the 'many' side that references the primary key of the 'one' side.

This strategy is so fundamental that it might appear self-evident. But understanding why this approach works—and why alternatives fail—provides deep insight into relational design principles. It also prepares you for edge cases where variations are necessary.

In this page, we rigorously examine the foreign key placement strategy, walk through the mapping algorithm step by step, and understand the logical basis for this design pattern.

What You Will Learn

By the end of this page, you will understand why foreign keys go on the N-side, how to implement this mapping, what happens when you try alternatives, and how to handle relationship attributes in 1:N mappings.

The 1:N Mapping Algorithm

The mapping of one-to-many relationships follows a straightforward algorithm. Let's define it precisely before examining why it works.

1:N Mapping Algorithm

•Map each entity type to a relation — Create a table for the parent entity (1-side) and a table for the child entity (N-side), each with their respective attributes.
•Identify the primary keys — The parent table has its own primary key (PK); the child table has its own primary key.
•Add foreign key to child table — Add a column to the child table (N-side) that stores the primary key value of the related parent entity.
•Define referential constraint — Create a foreign key constraint from the child table's new column to the parent table's primary key.
•Handle participation constraints — If the child has total participation (must belong to a parent), make the foreign key NOT NULL. If partial, allow NULL.
•Map relationship attributes — If the relationship has its own attributes, add them to the child table (the N-side).

Visual Example:

Consider the ER diagram relationship:

    ┌────────────────┐                    ┌────────────────┐
    │   DEPARTMENT   │                    │    EMPLOYEE    │
    │────────────────│      WORKS_IN      │────────────────│
    │ dept_id (PK)   │ 1 ────────────── N │ emp_id (PK)    │
    │ dept_name      │                    │ emp_name       │
    │ location       │                    │ salary         │
    └────────────────┘                    └────────────────┘

Resulting Relational Schema:

DEPARTMENT (dept_id, dept_name, location)
    PRIMARY KEY (dept_id)

EMPLOYEE (emp_id, emp_name, salary, dept_id)
    PRIMARY KEY (emp_id)
    FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id)

Notice how the dept_id appears in EMPLOYEE as a foreign key. This is the heart of 1:N mapping.

No Separate Relationship Table

Unlike M:N relationships which require a bridge/junction table, 1:N relationships do NOT require a separate table. The relationship is captured entirely by the foreign key in the existing child table. This is both simpler and more efficient.

Why the Foreign Key Goes on the N-Side

The placement of the foreign key on the N-side (child table) is not arbitrary—it's the only approach that preserves relational integrity without data redundancy. Let's understand why through logical analysis.

FK on N-Side (Correct)

•Each child row has exactly one parent reference
•No duplication of parent data
•Simple, single-valued column
•Natural representation of 1:N semantics
•Efficient storage and indexing

FK on 1-Side (Problems)

•Would need to store multiple child references
•Violates 1NF (multi-valued attribute)
•How many columns? Variable and unpredictable
•Updates become complex and error-prone
•Wasted space for parents with few children

The Logical Argument:

Consider attempting to place the foreign key on the 1-side (parent table). If Department stores references to Employees, we face an immediate problem: how many employee references should we store?

-- WRONG APPROACH: FK on parent side
DEPARTMENT (
    dept_id,
    dept_name,
    emp_id_1,  -- First employee?
    emp_id_2,  -- Second employee?
    emp_id_3,  -- Third employee?
    ...        -- How many columns?!
)

This approach fails for multiple reasons:

1NF Violation: Storing multiple employee IDs would require either multiple columns (violating atomicity if grouped) or repeated rows (creating redundancy).
Variable Cardinality: Different departments have different numbers of employees. We'd need either a fixed maximum (wasting space) or a variable number of columns (impossible in relational model).
Update Anomalies: Adding or removing an employee requires modifying the parent row, creating update anomalies.

By contrast, placing the FK on the N-side is elegant:

-- CORRECT APPROACH: FK on child side
EMPLOYEE (
    emp_id,
    emp_name,
    dept_id  -- Single-valued, references one parent
)

Each employee row has exactly one department reference. The relationship is fully represented without anomalies.

The First Normal Form Connection

This mapping strategy is deeply connected to First Normal Form (1NF). By placing the FK on the N-side, we ensure that each cell contains exactly one value (one parent reference). Placing it on the 1-side would require multi-valued cells or repeating groups—both 1NF violations.

Implementation Details

Let's examine the SQL implementation of 1:N mapping in detail, covering table creation, foreign key definition, and common variations.

1n_mapping_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- =========================================
-- Example: DEPARTMENT (1) → EMPLOYEE (N)
-- =========================================
 
-- Step 1: Create the parent table (1-side)
CREATE TABLE Department (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(100) NOT NULL,
    location    VARCHAR(100),
    budget      DECIMAL(15,2),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Step 2: Create the child table (N-side) with foreign key
CREATE TABLE Employee (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE,
    salary      DECIMAL(10,2),
    hire_date   DATE,
    
    -- Foreign key column (stores parent's PK)
    dept_id     INT NOT NULL,  -- NOT NULL = total participation
    
    -- Foreign key constraint
    CONSTRAINT fk_employee_department
        FOREIGN KEY (dept_id)
        REFERENCES Department(dept_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- Step 3: Create index on foreign key for performance
CREATE INDEX idx_employee_dept ON Employee(dept_id);

Key Implementation Notes:

1. Column Naming Conventions:

Use the same name as the parent's PK (dept_id) or a descriptive name (department_id)
Be consistent across your schema
Some teams prefix FK columns: fk_dept_id

2. Data Type Matching:

The FK column must match the parent PK's data type exactly
If parent uses BIGINT, child FK must use BIGINT
Type mismatches cause constraint creation failures

3. NOT NULL Decision:

NOT NULL ↔ Total participation (every child must have a parent)
Allow NULL ↔ Partial participation (child may exist without parent)

4. Constraint Naming:

Always name your constraints (fk_employee_department)
Default constraint names are often cryptic and hard to debug
Convention: fk_childtable_parenttable

Foreign Key Constraint Options
Clause	Options	Use Case
ON DELETE	RESTRICT, CASCADE, SET NULL, SET DEFAULT, NO ACTION	What happens when parent is deleted
ON UPDATE	RESTRICT, CASCADE, SET NULL, SET DEFAULT, NO ACTION	What happens when parent PK changes

Referential Actions and Cascade Behavior

When creating foreign key constraints, you must decide what happens when the referenced parent row is deleted or its primary key is updated. These decisions have significant implications for data integrity and application behavior.

ON DELETE Options for 1:N Relationships
Option	Behavior	When to Use
RESTRICT	Prevents parent deletion if children exist	Strong child entities that should outlive parent
CASCADE	Deletes all children when parent is deleted	Weak entities, existence-dependent children
SET NULL	Sets FK to NULL when parent is deleted	Optional relationships, preserving orphaned data
SET DEFAULT	Sets FK to default value when parent deleted	Reassigning to default parent (e.g., 'Unassigned')
NO ACTION	Similar to RESTRICT (timing differs by DBMS)	Same as RESTRICT in most practical cases

referential_actions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Example 1: RESTRICT (default, safest)
-- Parent cannot be deleted while children exist
ALTER TABLE Employee
ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
 
-- Example 2: CASCADE (for weak/dependent entities)
-- Deleting an order deletes all its line items
ALTER TABLE OrderLine
ADD CONSTRAINT fk_orderline_order
    FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
 
-- Example 3: SET NULL (optional relationship)
-- Deleting a manager leaves employees without a manager
ALTER TABLE Employee
ADD CONSTRAINT fk_emp_manager
    FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;
 
-- Example 4: SET DEFAULT (reassign to placeholder)
ALTER TABLE Product
ADD CONSTRAINT fk_product_category
    FOREIGN KEY (category_id) REFERENCES Category(category_id)
    ON DELETE SET DEFAULT
    ON UPDATE CASCADE;

CASCADE with Caution

CASCADE deletions can remove large amounts of data with a single DELETE statement. While appropriate for weak entities (order lines, comments), use carefully for important data. Accidental deletion of a parent cascades to all children with no undo. Consider RESTRICT for important entities and handle deletion logic in application code.

Choosing the Right Referential Action

•Weak entities (existence-dependent) → CASCADE. If the parent's deletion makes the child meaningless, cascade is appropriate. Order → OrderLine.
•Strong entities with optional relationship → SET NULL or SET DEFAULT. Employees can exist without a manager; set to NULL.
•Strong entities with required relationship → RESTRICT. Employees must have a department; don't allow department deletion.
•Audit/history data → RESTRICT or application logic. Never cascade delete financial transactions or audit logs.
•Self-referential hierarchies → SET NULL for management trees, CASCADE for true tree structures.

Handling Relationship Attributes in 1:N

Sometimes a relationship itself has attributes—properties that belong to the association between entities, not to either entity individually. In 1:N relationships, these attributes are placed in the child table alongside the foreign key.

Example: WORKS_IN with Start Date

Consider Department (1) → Employee (N) with the relationship attribute start_date (when the employee started working in that department).

start_date doesn't belong to EMPLOYEE (it's specific to this position in this department)
start_date doesn't belong to DEPARTMENT (it's about this employee)
start_date belongs to the WORKS_IN relationship

Mapping: Add start_date to the EMPLOYEE table:

CREATE TABLE Employee (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    salary      DECIMAL(10,2),
    
    -- Foreign key
    dept_id     INT NOT NULL REFERENCES Department(dept_id),
    
    -- Relationship attribute
    start_date  DATE NOT NULL  -- Attribute of WORKS_IN
);

Why the Child Table?

Relationship attributes go on the child table because that's where the foreign key resides. The child table already represents the relationship (through the FK), so it's the natural place for relationship attributes. Each child row describes one instance of the relationship, so relationship attributes have a natural home there.

More Examples:

Relationship	Parent	Child	Relationship Attribute	Placement
ENROLLS	Course	Enrollment*	grade, enrollment_date	Enrollment table
ASSIGNS	Project	Task	assigned_date, priority	Task table
CONTAINS	Folder	File	added_date, added_by	File table
EMPLOYS	Department	Employee	start_date, role	Employee table

*Note: If Enrollment is modeled as a separate entity (often done when enrollment has significant attributes), it becomes the child in a 1:N between Course and Enrollment.

Important Distinction:

Don't confuse relationship attributes with child entity attributes:

salary is an Employee attribute (belongs to the employee regardless of department)
start_date is a relationship attribute (specific to this employee-department pair)

In practice, this distinction matters if the relationship can change. If an employee moves to a new department, start_date should represent the new assignment, while salary remains the employee's property.

Indexing Foreign Keys for Performance

A critical but often overlooked aspect of 1:N mapping is index creation on foreign key columns. While some databases automatically index FKs, others don't, and understanding when and why to index is essential for performance.

Index Your Foreign Keys

Foreign keys are NOT automatically indexed by all databases. PostgreSQL and Oracle do NOT auto-index FKs, while MySQL (InnoDB) does. Without indexes, joins and constraint checks become full table scans, devastating performance at scale.

Why Index Foreign Keys?

JOIN Performance: Queries joining parent and child tables use the FK column. Without an index, the database scans the entire child table for each parent row.
Referential Constraint Checking: When deleting/updating a parent row, the database must check for referencing children. Without an index, this requires a full table scan.
Parent-to-Children Queries: Finding all employees in a department (WHERE dept_id = ?) benefits enormously from an index.

Query Examples Benefiting from FK Index:

-- JOIN (uses index on Employee.dept_id)
SELECT e.emp_name, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- Finding children (uses index on Employee.dept_id)
SELECT * FROM Employee WHERE dept_id = 100;

-- Parent deletion check (uses index on Employee.dept_id)
DELETE FROM Department WHERE dept_id = 100;
-- Database must verify no employees reference this department

fk_indexing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Creating FK with explicit index
CREATE TABLE Employee (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    dept_id     INT NOT NULL REFERENCES Department(dept_id)
);
 
-- Create index on foreign key column
CREATE INDEX idx_employee_dept_id ON Employee(dept_id);
 
-- For composite foreign keys
CREATE TABLE OrderLine (
    order_id    INT,
    line_num    INT,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, line_num),
    FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
);
 
-- Index for the FK
CREATE INDEX idx_orderline_order ON OrderLine(order_id);
CREATE INDEX idx_orderline_product ON OrderLine(product_id);
 
-- If FK is part of PK, it's already indexed
-- In OrderLine above, order_id is indexed via PRIMARY KEY

Database FK Auto-Indexing Behavior
Database	Auto-Index FK?	Recommendation
MySQL (InnoDB)	Yes	Index created automatically; verify in production
PostgreSQL	No	Always create indexes on FK columns manually
Oracle	No	Always create indexes on FK columns manually
SQL Server	No	Create indexes; may be suggested by tuning advisor
SQLite	No	Create indexes for any significant data volume

Complete Mapping Example

Let's work through a complete example from ER diagram to implemented schema, demonstrating all the concepts covered.

Scenario: Online Bookstore

┌────────────────┐                    ┌────────────────┐
│    AUTHOR      │      WRITES        │     BOOK       │
│────────────────│  (publish_date,    │────────────────│
│ author_id (PK) │   royalty_rate)    │ isbn (PK)      │
│ name           │ 1 ────────────── N │ title          │
│ country        │                    │ price          │
│ birth_date     │                    │ pages          │
└────────────────┘                    └────────────────┘

Business Rules:
- Each book is written by exactly one author (total participation)
- An author may have written zero or more books (partial participation)
- The relationship has attributes: publish_date, royalty_rate

bookstore_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- =========================================
-- Complete 1:N Mapping: Author → Book
-- =========================================
 
-- Parent table: AUTHOR (1-side)
CREATE TABLE Author (
    author_id   SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    country     VARCHAR(100),
    birth_date  DATE,
    biography   TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Child table: BOOK (N-side)
-- Includes FK to Author and relationship attributes
CREATE TABLE Book (
    isbn            VARCHAR(13) PRIMARY KEY,  -- ISBN-13 standard
    title           VARCHAR(500) NOT NULL,
    price           DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    pages           INT CHECK (pages > 0),
    publication_year INT,
    genre           VARCHAR(50),
    
    -- Foreign key (1:N mapping)
    author_id       INT NOT NULL,  -- NOT NULL: total participation
    
    -- Relationship attributes (belong to WRITES relationship)
    publish_date    DATE,          -- When this book was published
    royalty_rate    DECIMAL(5,2) CHECK (royalty_rate BETWEEN 0 AND 100),
    
    -- Foreign key constraint
    CONSTRAINT fk_book_author
        FOREIGN KEY (author_id)
        REFERENCES Author(author_id)
        ON DELETE RESTRICT     -- Don't delete authors with books
        ON UPDATE CASCADE,     -- Cascade author_id changes
        
    CONSTRAINT chk_publish_year
        CHECK (publication_year BETWEEN 1800 AND 2100)
);
 
-- Performance: Index on foreign key
CREATE INDEX idx_book_author ON Book(author_id);
 
-- Additional useful indexes
CREATE INDEX idx_book_genre ON Book(genre);
CREATE INDEX idx_book_year ON Book(publication_year);
 
-- =========================================
-- Sample Queries Demonstrating the Join
-- =========================================
 
-- All books by a specific author
SELECT b.title, b.price, b.pages, b.publish_date
FROM Book b
WHERE b.author_id = 1;
 
-- Author info with their book count
SELECT a.name, a.country, COUNT(b.isbn) AS book_count
FROM Author a
LEFT JOIN Book b ON a.author_id = b.author_id
GROUP BY a.author_id, a.name, a.country;
 
-- Books with author details (uses FK index)
SELECT a.name AS author, b.title, b.price, b.royalty_rate
FROM Book b
JOIN Author a ON b.author_id = a.author_id
ORDER BY a.name, b.title;

Summary: Foreign Key on the N-Side

We've thoroughly explored the fundamental strategy for mapping 1:N relationships to relational tables. Let's consolidate the key principles:

Key Takeaways

•FK always goes on the N-side — This is not a choice but a logical necessity. Placing it on the 1-side would violate 1NF and create update anomalies.
•No separate relationship table — Unlike M:N, 1:N relationships are captured entirely by the FK in the child table. Simpler and more efficient.
•NOT NULL reflects participation — If the child has total participation (must have a parent), use NOT NULL on the FK. Otherwise, allow NULL.
•Choose referential actions carefully — RESTRICT, CASCADE, SET NULL each have appropriate use cases. CASCADE is for weak entities; RESTRICT for important data.
•Relationship attributes go on child table — Since the child table embodies the relationship (via FK), relationship attributes naturally belong there.
•Index your foreign keys — Not all databases auto-index FKs. Manual indexes on FK columns are essential for JOIN and constraint-checking performance.

Looking Ahead:

Now that we understand the mechanical process of 1:N mapping, the next page explores participation constraints in greater depth. We'll examine how total vs. partial participation on both sides affects schema design, NULL handling, and constraint enforcement.

Page Complete

You now understand the core 1:N mapping strategy: foreign key placement on the N-side, the logical basis for this approach, implementation details, referential actions, relationship attributes, and indexing. This is the foundation for most relationship mappings you'll encounter.