System Design (LLD)Data Modeling for LLD

Data Modeling for Low-Level Design

LevelIntermediate

Duration90 mins

TopicData Modeling for LLD

2 / 4

One-to-One, One-to-Many, Many-to-Many Relationships

The Three Fundamental Cardinalities

Every relationship between entities falls into one of three cardinality patterns:

One-to-One (1:1): Each entity on one side relates to exactly one entity on the other
One-to-Many (1:N): One entity relates to multiple entities on the other side
Many-to-Many (M:N): Multiple entities relate to multiple entities

While these categories seem simple, each has distinct implementation patterns, performance implications, and design considerations. Understanding these differences deeply is essential for building systems that are both correct and performant.

This page examines each relationship type in exhaustive detail—from database schema design through object model implementation to the subtle traps that ensnare even experienced engineers.

What You Will Master

By the end of this page, you will understand: (1) When to choose each relationship type, (2) How to implement each type in code and database schema, (3) Performance characteristics and optimization strategies, (4) Navigation patterns and their implications, (5) Common mistakes and how to avoid them.

One-to-One Relationships

A one-to-one (1:1) relationship exists when exactly one instance of Entity A is associated with exactly one instance of Entity B. This is the rarest relationship type in practice, but it serves important purposes.

Examples of one-to-one relationships:

User ↔ UserProfile (profile data separated for performance or security)
Employee ↔ ParkingSpot (one assigned spot per employee)
Country ↔ Capital (each country has exactly one capital)
Order ↔ Invoice (each order generates exactly one invoice)

Why Use One-to-One Relationships?

If two entities have a 1:1 relationship, why not just combine them into one table or class? There are several valid reasons:

Performance optimization: Separate frequently-accessed fields from rarely-accessed fields. If User has 50 fields but most operations only need 5, put the other 45 in UserProfile.
Security isolation: Sensitive data (SSN, salary) in a separate entity allows different access control.
Optional extension: The related entity might be optional. Not all Users have Profiles.
Inheritance representation: Subtype data stored in a separate table (e.g., Employee base table + Manager attributes table).
Third-party integration: External systems might require separate storage (ShippingLabel from FedEx for an Order).

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
70
// One-to-One: User and UserProfile
// The profile extends user with optional, detailed information
 
class User {
  private readonly id: string;
  private email: string;
  private passwordHash: string;
  private profile: UserProfile | null = null; // Optional 1:1
  
  constructor(id: string, email: string, passwordHash: string) {
    this.id = id;
    this.email = email;
    this.passwordHash = passwordHash;
  }
  
  // Lazy accessor - profile might not be loaded
  getProfile(): UserProfile | null {
    return this.profile;
  }
  
  hasProfile(): boolean {
    return this.profile !== null;
  }
  
  // Factory method ensures proper bidirectional setup
  createProfile(bio: string, avatarUrl: string): UserProfile {
    if (this.profile !== null) {
      throw new Error("User already has a profile");
    }
    
    this.profile = new UserProfile(this, bio, avatarUrl);
    return this.profile;
  }
  
  // Internal method for profile deletion
  _clearProfile(): void {
    this.profile = null;
  }
}
 
class UserProfile {
  private readonly id: string;
  private readonly user: User; // Required back-reference
  private bio: string;
  private avatarUrl: string;
  private socialLinks: Map<string, string> = new Map();
  
  constructor(user: User, bio: string, avatarUrl: string) {
    this.id = generateId();
    this.user = user;
    this.bio = bio;
    this.avatarUrl = avatarUrl;
  }
  
  getUser(): User {
    return this.user;
  }
  
  updateBio(bio: string): void {
    if (bio.length > 500) {
      throw new Error("Bio cannot exceed 500 characters");
    }
    this.bio = bio;
  }
  
  delete(): void {
    this.user._clearProfile();
    // ORM/repository handles database deletion
  }
}

Database Schema for One-to-One:

One-to-one relationships in the database can be implemented in two ways:

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
-- Option 1: Foreign key on the dependent table
-- Profile depends on User - profile cannot exist without user
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE user_profiles (
    id UUID PRIMARY KEY,
    user_id UUID UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
-- Option 2: Shared primary key
-- Profile uses the same ID as User - tighter coupling
CREATE TABLE user_profiles (
    user_id UUID PRIMARY KEY,  -- Same ID as User
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
-- Query both together
SELECT u.*, p.bio, p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = ?;

Choosing the Primary Side

In a 1:1 relationship, one side is typically 'primary' (exists independently) and one is 'dependent' (requires the primary). Place the foreign key on the dependent side. User can exist without Profile, so Profile holds the foreign key to User.

One-to-Many Relationships

A one-to-many (1:N) relationship is the most common relationship type. One entity on the 'one' side relates to multiple entities on the 'many' side.

Examples of one-to-many relationships:

Customer → Orders (one customer places many orders)
Order → OrderItems (one order contains many items)
Department → Employees (one department has many employees)
BlogPost → Comments (one post has many comments)
Directory → Files (one directory contains many files)

The Two Perspectives:

A one-to-many relationship actually describes two inverse relationships:

From the 'one' side: parent → children (one-to-many)
From the 'many' side: child → parent (many-to-one)

Both perspectives describe the same underlying relationship, but they have different implications for navigation and data loading.

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
// One-to-Many: Customer has many Orders
// Demonstrates both perspectives and proper encapsulation
 
class Customer {
  private readonly id: string;
  private name: string;
  private email: string;
  private orders: Order[] = []; // The 'one' side holds a collection
  
  constructor(id: string, name: string, email: string) {
    this.id = id;
    this.name = name;
    this.email = email;
  }
  
  getId(): string {
    return this.id;
  }
  
  // Expose immutable view of orders
  getOrders(): ReadonlyArray<Order> {
    return this.orders;
  }
  
  getOrderCount(): number {
    return this.orders.length;
  }
  
  // Factory method creates order with proper bidirectional link
  placeOrder(items: OrderItemData[]): Order {
    const order = new Order(this, items);
    this.orders.push(order);
    return order;
  }
  
  // Calculate aggregate across related entities
  getTotalSpent(): Money {
    return this.orders
      .filter(o => o.getStatus() === OrderStatus.COMPLETED)
      .reduce((sum, o) => sum.add(o.getTotal()), Money.ZERO);
  }
  
  // Internal method for order cancellation
  _removeOrder(order: Order): void {
    const index = this.orders.indexOf(order);
    if (index > -1) {
      this.orders.splice(index, 1);
    }
  }
}
 
class Order {
  private readonly id: string;
  private readonly customer: Customer; // The 'many' side holds a reference
  private readonly orderDate: Date;
  private status: OrderStatus;
  private items: OrderItem[] = [];
  
  constructor(customer: Customer, itemData: OrderItemData[]) {
    this.id = generateId();
    this.customer = customer;
    this.orderDate = new Date();
    this.status = OrderStatus.PENDING;
    
    // Create items from data
    for (const data of itemData) {
      this.items.push(new OrderItem(this, data.product, data.quantity));
    }
  }
  
  // Navigate to parent
  getCustomer(): Customer {
    return this.customer;
  }
  
  // Convenient accessor for customer ID without loading full customer
  getCustomerId(): string {
    return this.customer.getId();
  }
  
  getStatus(): OrderStatus {
    return this.status;
  }
  
  getTotal(): Money {
    return this.items.reduce(
      (sum, item) => sum.add(item.getSubtotal()),
      Money.ZERO
    );
  }
  
  cancel(): void {
    if (this.status !== OrderStatus.PENDING) {
      throw new Error("Only pending orders can be cancelled");
    }
    this.status = OrderStatus.CANCELLED;
    this.customer._removeOrder(this);
  }
}

Database Schema for One-to-Many:

The foreign key is always on the 'many' side, pointing to the 'one' side:

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
-- One-to-Many: Customer has many Orders
CREATE TABLE customers (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    customer_id UUID NOT NULL,  -- Foreign key on 'many' side
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT  -- Don't delete customer with orders
);
 
-- Index on foreign key for efficient joins
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
 
-- Efficient queries
-- Get customer's orders (follow 1:N)
SELECT * FROM orders WHERE customer_id = ? ORDER BY order_date DESC;
 
-- Get customer for an order (follow N:1)
SELECT c.* FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.id = ?;
 
-- Count orders per customer
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

The Collection Loading Problem

When a Customer has 10,000 Orders, loading all orders eagerly is a disaster. One-to-many collections must be lazily loaded or paginated. Never expose a raw collection that might contain unbounded data—always provide methods that limit or paginate results.

Many-to-Many Relationships

A many-to-many (M:N) relationship exists when multiple instances of Entity A can relate to multiple instances of Entity B, and vice versa.

Examples of many-to-many relationships:

Students ↔ Courses (students enroll in multiple courses; courses have multiple students)
Products ↔ Categories (products belong to multiple categories; categories contain multiple products)
Users ↔ Roles (users have multiple roles; roles are assigned to multiple users)
Authors ↔ Books (books can have multiple authors; authors write multiple books)
Actors ↔ Movies (actors appear in multiple movies; movies have multiple actors)

The Join Entity:

Many-to-many relationships cannot be directly represented in a relational database—there's no place for the foreign keys. Instead, we use a join table (also called junction table, association table, or link table).

In code, this join table might be:

Implicit: The ORM handles it automatically, exposing just the two collections
Explicit: A first-class entity when the relationship itself has attributes

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
// Many-to-Many with NO additional attributes
// The relationship itself is simple - just a link
// Often handled implicitly by ORM
 
class Product {
  private readonly id: string;
  private name: string;
  private categories: Set<Category> = new Set(); // Simple M:N
  
  getCategories(): ReadonlySet<Category> {
    return this.categories;
  }
  
  addToCategory(category: Category): void {
    if (!this.categories.has(category)) {
      this.categories.add(category);
      category._addProduct(this); // Bidirectional sync
    }
  }
  
  removeFromCategory(category: Category): void {
    if (this.categories.delete(category)) {
      category._removeProduct(this); // Bidirectional sync
    }
  }
  
  _addCategory(category: Category): void {
    this.categories.add(category);
  }
  
  _removeCategory(category: Category): void {
    this.categories.delete(category);
  }
}
 
class Category {
  private readonly id: string;
  private name: string;
  private products: Set<Product> = new Set();
  
  getProducts(): ReadonlySet<Product> {
    return this.products;
  }
  
  addProduct(product: Product): void {
    if (!this.products.has(product)) {
      this.products.add(product);
      product._addCategory(this);
    }
  }
  
  _addProduct(product: Product): void {
    this.products.add(product);
  }
  
  _removeProduct(product: Product): void {
    this.products.delete(product);
  }
}

Many-to-Many with Relationship Attributes:

When the relationship itself carries data (enrollment date, role in movie, quantity in order), the join table becomes a first-class entity:

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
// Many-to-Many WITH relationship attributes
// The relationship is a first-class entity: Enrollment
 
class Student {
  private readonly id: string;
  private name: string;
  private enrollments: Enrollment[] = []; // Explicit join entities
  
  getEnrollments(): ReadonlyArray<Enrollment> {
    return this.enrollments;
  }
  
  // Navigate through join entity to get courses
  getCourses(): Course[] {
    return this.enrollments.map(e => e.getCourse());
  }
  
  enrollIn(course: Course, semester: Semester): Enrollment {
    // Check for duplicate enrollment
    const existing = this.enrollments.find(
      e => e.getCourse() === course && e.getSemester() === semester
    );
    if (existing) {
      throw new Error("Already enrolled in this course for this semester");
    }
    
    const enrollment = new Enrollment(this, course, semester);
    this.enrollments.push(enrollment);
    course._addEnrollment(enrollment);
    return enrollment;
  }
  
  _addEnrollment(enrollment: Enrollment): void {
    this.enrollments.push(enrollment);
  }
}
 
class Course {
  private readonly id: string;
  private name: string;
  private maxCapacity: number;
  private enrollments: Enrollment[] = [];
  
  getEnrollments(): ReadonlyArray<Enrollment> {
    return this.enrollments;
  }
  
  getStudents(): Student[] {
    return this.enrollments.map(e => e.getStudent());
  }
  
  getEnrollmentCount(): number {
    return this.enrollments.length;
  }
  
  hasCapacity(): boolean {
    return this.enrollments.length < this.maxCapacity;
  }
  
  _addEnrollment(enrollment: Enrollment): void {
    if (!this.hasCapacity()) {
      throw new Error("Course is at capacity");
    }
    this.enrollments.push(enrollment);
  }
}
 
// The JOIN ENTITY - represents the relationship with its own data
class Enrollment {
  private readonly id: string;
  private readonly student: Student;
  private readonly course: Course;
  private readonly semester: Semester;
  private readonly enrollmentDate: Date;
  private grade: Grade | null = null;
  private status: EnrollmentStatus;
  
  constructor(student: Student, course: Course, semester: Semester) {
    this.id = generateId();
    this.student = student;
    this.course = course;
    this.semester = semester;
    this.enrollmentDate = new Date();
    this.status = EnrollmentStatus.ACTIVE;
  }
  
  getStudent(): Student {
    return this.student;
  }
  
  getCourse(): Course {
    return this.course;
  }
  
  getSemester(): Semester {
    return this.semester;
  }
  
  assignGrade(grade: Grade): void {
    if (this.status !== EnrollmentStatus.ACTIVE) {
      throw new Error("Cannot grade dropped enrollment");
    }
    this.grade = grade;
    this.status = EnrollmentStatus.COMPLETED;
  }
  
  drop(): void {
    if (this.status !== EnrollmentStatus.ACTIVE) {
      throw new Error("Cannot drop non-active enrollment");
    }
    this.status = EnrollmentStatus.DROPPED;
  }
}

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
-- Many-to-Many: Students and Courses linked by Enrollments
CREATE TABLE students (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);
 
CREATE TABLE courses (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    max_capacity INTEGER NOT NULL DEFAULT 30
);
 
-- Join table with its own attributes
CREATE TABLE enrollments (
    id UUID PRIMARY KEY,
    student_id UUID NOT NULL,
    course_id UUID NOT NULL,
    semester VARCHAR(20) NOT NULL,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade VARCHAR(2),
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    
    -- Composite unique constraint: one enrollment per student/course/semester
    UNIQUE(student_id, course_id, semester)
);
 
-- Indexes for common query patterns
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_enrollments_semester ON enrollments(semester);
 
-- Query: Get all courses for a student
SELECT c.*, e.grade, e.status
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE e.student_id = ? AND e.semester = ?;
 
-- Query: Get all students in a course
SELECT s.*, e.grade, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.course_id = ? AND e.semester = ?;

When to Promote the Join Table

If your join table has ANY attributes beyond the two foreign keys, make it a first-class entity. Even if it starts simple (just linking products to categories), consider whether you might later need: created_at, created_by, sort_order, is_primary, etc. Promoting early is easier than refactoring later.

Relationship Types Compared

Let's consolidate the key differences between the three relationship types:

Relationship Type Comparison
Aspect	One-to-One (1:1)	One-to-Many (1:N)	Many-to-Many (M:N)
Frequency	Rare	Very common	Common
Foreign key location	Dependent side	'Many' side	Join table
Code representation (one side)	Single reference	Collection	Collection (often via join entity)
Code representation (other side)	Single reference (or none)	Single reference	Collection (often via join entity)
Typical loading strategy	Eager (usually small)	Lazy (possibly large)	Lazy (often large on both sides)
Cascade delete	Often cascade	Depends on ownership	Usually delete join records only
Primary use case	Separate concerns, optional data	Parent-child, ownership	Cross-cutting associations

Performance Considerations by Relationship Type

Each relationship type has distinct performance characteristics that affect query patterns, memory usage, and scalability.

One-to-One Performance

•Join cost: Single join is cheap; often loaded together via LEFT JOIN.
•Memory: Combined object size is predictable and bounded.
•Optimization: Can denormalize into single table if always accessed together.
•Risk: Over-separation leads to unnecessary joins. Only split if there's a genuine reason.

One-to-Many Performance

•Collection size variance: From 0 to millions. Must handle both extremes.
•The N+1 problem: Loading parent then lazy-loading each child = N+1 queries.
•Pagination essential: Never load unbounded collections. Always paginate or limit.
•Index critical: Foreign key column MUST be indexed for JOIN performance.
•Count vs load: Provide count() methods that don't load full collection.

Many-to-Many Performance

•Double join cost: Every traversal requires two joins (entity → join table → entity).
•Explosive combinations: M items × N items creates M×N join records. Can grow rapidly.
•Index both foreign keys: Join table needs indexes on both foreign key columns.
•Composite index for uniqueness: (entity_a_id, entity_b_id) composite index.
•Beware Cartesian products: Incorrect joins can explode row counts catastrophically.

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
// Performance-aware relationship access patterns
 
class Customer {
  private orders: Order[] | null = null; // Lazy, nullable
  private orderCount: number | null = null; // Cached count
  
  // DON'T: Load all orders just to count them
  // getOrderCount(): number {
  //   return this.getOrders().length; // Triggers full load!
  // }
  
  // DO: Separate count method that doesn't load collection
  async getOrderCount(): Promise<number> {
    if (this.orderCount === null) {
      this.orderCount = await this.orderRepository.countByCustomer(this.id);
    }
    return this.orderCount;
  }
  
  // DO: Paginated access
  async getOrdersPage(page: number, pageSize: number): Promise<Order[]> {
    return this.orderRepository.findByCustomer(this.id, {
      offset: page * pageSize,
      limit: pageSize,
      orderBy: 'orderDate DESC'
    });
  }
  
  // DO: Filtered access that limits results
  async getRecentOrders(limit: number = 10): Promise<Order[]> {
    return this.orderRepository.findByCustomer(this.id, {
      limit,
      orderBy: 'orderDate DESC'
    });
  }
  
  // DON'T: Expose raw collection that might be massive
  // getOrders(): Order[] { return this.orders; }
}

The N+1 Query Disaster

The most common performance killer: loading a list of parents, then lazy-loading children for each. For 100 customers with orders: 1 query for customers + 100 queries for orders = 101 queries instead of 2. Always use eager loading with JOIN or batch loading when you know you need related data.

Navigation Patterns

How you traverse relationships affects both code clarity and performance. Different patterns suit different access needs.

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
// Pattern 1: Direct object navigation
// Natural OO style, but may trigger lazy loading
const customer = order.getCustomer();
const customerName = order.getCustomer().getName();
 
// Pattern 2: ID-only navigation (avoids loading)
// Use when you only need the ID for further queries
const customerId = order.getCustomerId(); // Just returns stored ID
const customerOrders = orderRepo.findByCustomerId(customerId);
 
// Pattern 3: Explicit loading control
// Clear about what's loaded and when
const order = await orderRepo.findById(orderId);
await order.loadCustomer(); // Explicit load
await order.loadItems();    // Explicit load
// Now safe to navigate
 
// Pattern 4: Eager loading at query time
// Best for known access patterns
const order = await orderRepo.findByIdWithDetails(orderId, {
  include: ['customer', 'items', 'items.product']
});
// All related data loaded in single query
 
// Pattern 5: Projection / DTO
// Return only needed data, skip entity materialization
interface OrderSummary {
  orderId: string;
  orderDate: Date;
  customerName: string;  // Flattened - no customer object
  totalAmount: number;
  itemCount: number;
}
 
const summary = await orderRepo.getOrderSummary(orderId);
// Single query, single result, no lazy loading concerns

Navigation Pattern Trade-offs
Pattern	When to Use	Pros	Cons
Direct navigation	Small graphs, known eager loaded	Natural OO style	Hidden lazy loads, N+1 risk
ID-only navigation	Cross-aggregate references	No unnecessary loads	Requires separate queries
Explicit loading	Complex operations on entities	Clear load timing	Verbose code
Eager loading	Known access patterns	Optimal query count	May over-fetch
Projection/DTO	Read-only, reporting, APIs	Minimal data transfer	No entity behavior

Common Mistakes in Relationship Modeling

Years of production experience reveal these recurring mistakes across teams and projects:

Mistakes to Avoid

•Modeling M:N as two 1:N relationships — If the relationship has no identity or attributes, use a proper M:N with implicit join table. Two 1:N relationships create orphan-able join records.
•Forcing 1:1 when 1:N would naturally fit — 'A User has one Address' seems clean until users need billing AND shipping addresses. Model for the actual domain, not perceived simplicity.
•Bidirectional when unidirectional suffices — Every bidirectional relationship doubles maintenance burden. Add back-references only when you genuinely need reverse navigation.
•Loading collections to check membership — Don't load 10,000 items to check if one exists. Use repository methods: exists(parentId, childId).
•Ignoring relationship cardinality changes — A 'User has one Department' becomes M:N when users can belong to multiple departments. Build flexibility for likely changes.
•Treating ORM magic as free — Lazy loading, dirty checking, and cascade operations have costs. Understand what your ORM generates before production.
•Cascading deletes without thought — Deleting a Customer that cascades to Orders that cascade to Payments... might delete financial records you're legally required to keep.

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
// ❌ MISTAKE: Loading collection to check membership
class Team {
  async hasMember(userId: string): Promise<boolean> {
    const members = await this.getMembers(); // Loads all members!
    return members.some(m => m.id === userId);
  }
}
 
// ✅ CORRECT: Repository method that checks directly
class Team {
  async hasMember(userId: string): Promise<boolean> {
    return this.membershipRepo.exists(this.id, userId);
    // SELECT EXISTS(SELECT 1 FROM memberships WHERE team_id = ? AND user_id = ?)
  }
}
 
// ❌ MISTAKE: Circular cascade
class Department {
  @OneToMany(() => Employee, { cascade: true })
  employees: Employee[];
}
class Employee {
  @ManyToOne(() => Department, { cascade: true }) // Danger!
  department: Department;
}
// Deleting either could cascade indefinitely
 
// ✅ CORRECT: One-way cascade matching ownership
class Department {
  @OneToMany(() => Employee, { cascade: ['insert', 'update'] })
  employees: Employee[];
}
class Employee {
  @ManyToOne(() => Department)  // No cascade back
  department: Department;
}

Summary: Relationship Types

Key Takeaways

•One-to-One relationships separate concerns—use them for optional data, security isolation, or performance optimization, not as default organization.
•One-to-Many is the workhorse relationship—understand it deeply, especially collection management, lazy loading, and the N+1 problem.
•Many-to-Many requires a join table—promote it to a first-class entity when the relationship carries any additional attributes.
•Foreign keys go on the 'many' side for 1:N, or in a join table for M:N.
•Performance varies dramatically by relationship type—always consider collection sizes and loading strategies.
•Navigation patterns should be explicit—don't hide lazy loading behind innocent-looking property access.
•Avoid common mistakes like bidirectionality by default, loading collections for membership checks, or thoughtless cascade deletes.

What's next:

Now that we understand relationship cardinalities, the next page dives into lazy vs eager loading—the strategies for when and how related data is fetched from the database. This is where the theoretical knowledge of relationships meets the practical reality of database performance.

Relationship Types Mastered

You now understand the three fundamental relationship cardinalities: when to use each, how to implement them in code and database, and the performance characteristics of each. Next, we'll learn how to control when related data is loaded.

2 / 4

Loading learning content...

System Design (LLD)Data Modeling for LLD

Data Modeling for Low-Level Design

LevelIntermediate

Duration90 mins

TopicData Modeling for LLD

2 / 4

One-to-One, One-to-Many, Many-to-Many Relationships

The Three Fundamental Cardinalities

Every relationship between entities falls into one of three cardinality patterns:

One-to-One (1:1): Each entity on one side relates to exactly one entity on the other
One-to-Many (1:N): One entity relates to multiple entities on the other side
Many-to-Many (M:N): Multiple entities relate to multiple entities

This page examines each relationship type in exhaustive detail—from database schema design through object model implementation to the subtle traps that ensnare even experienced engineers.

What You Will Master

One-to-One Relationships

Examples of one-to-one relationships:

User ↔ UserProfile (profile data separated for performance or security)
Employee ↔ ParkingSpot (one assigned spot per employee)
Country ↔ Capital (each country has exactly one capital)
Order ↔ Invoice (each order generates exactly one invoice)

Why Use One-to-One Relationships?

If two entities have a 1:1 relationship, why not just combine them into one table or class? There are several valid reasons:

Performance optimization: Separate frequently-accessed fields from rarely-accessed fields. If User has 50 fields but most operations only need 5, put the other 45 in UserProfile.
Security isolation: Sensitive data (SSN, salary) in a separate entity allows different access control.
Optional extension: The related entity might be optional. Not all Users have Profiles.
Inheritance representation: Subtype data stored in a separate table (e.g., Employee base table + Manager attributes table).
Third-party integration: External systems might require separate storage (ShippingLabel from FedEx for an Order).

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
70
// One-to-One: User and UserProfile
// The profile extends user with optional, detailed information
 
class User {
  private readonly id: string;
  private email: string;
  private passwordHash: string;
  private profile: UserProfile | null = null; // Optional 1:1
  
  constructor(id: string, email: string, passwordHash: string) {
    this.id = id;
    this.email = email;
    this.passwordHash = passwordHash;
  }
  
  // Lazy accessor - profile might not be loaded
  getProfile(): UserProfile | null {
    return this.profile;
  }
  
  hasProfile(): boolean {
    return this.profile !== null;
  }
  
  // Factory method ensures proper bidirectional setup
  createProfile(bio: string, avatarUrl: string): UserProfile {
    if (this.profile !== null) {
      throw new Error("User already has a profile");
    }
    
    this.profile = new UserProfile(this, bio, avatarUrl);
    return this.profile;
  }
  
  // Internal method for profile deletion
  _clearProfile(): void {
    this.profile = null;
  }
}
 
class UserProfile {
  private readonly id: string;
  private readonly user: User; // Required back-reference
  private bio: string;
  private avatarUrl: string;
  private socialLinks: Map<string, string> = new Map();
  
  constructor(user: User, bio: string, avatarUrl: string) {
    this.id = generateId();
    this.user = user;
    this.bio = bio;
    this.avatarUrl = avatarUrl;
  }
  
  getUser(): User {
    return this.user;
  }
  
  updateBio(bio: string): void {
    if (bio.length > 500) {
      throw new Error("Bio cannot exceed 500 characters");
    }
    this.bio = bio;
  }
  
  delete(): void {
    this.user._clearProfile();
    // ORM/repository handles database deletion
  }
}

Database Schema for One-to-One:

One-to-one relationships in the database can be implemented in two ways:

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
-- Option 1: Foreign key on the dependent table
-- Profile depends on User - profile cannot exist without user
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE user_profiles (
    id UUID PRIMARY KEY,
    user_id UUID UNIQUE NOT NULL,  -- UNIQUE enforces 1:1
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
-- Option 2: Shared primary key
-- Profile uses the same ID as User - tighter coupling
CREATE TABLE user_profiles (
    user_id UUID PRIMARY KEY,  -- Same ID as User
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
 
-- Query both together
SELECT u.*, p.bio, p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = ?;

Choosing the Primary Side

One-to-Many Relationships

A one-to-many (1:N) relationship is the most common relationship type. One entity on the 'one' side relates to multiple entities on the 'many' side.

Examples of one-to-many relationships:

Customer → Orders (one customer places many orders)
Order → OrderItems (one order contains many items)
Department → Employees (one department has many employees)
BlogPost → Comments (one post has many comments)
Directory → Files (one directory contains many files)

The Two Perspectives:

A one-to-many relationship actually describes two inverse relationships:

From the 'one' side: parent → children (one-to-many)
From the 'many' side: child → parent (many-to-one)

Both perspectives describe the same underlying relationship, but they have different implications for navigation and data loading.

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
// One-to-Many: Customer has many Orders
// Demonstrates both perspectives and proper encapsulation
 
class Customer {
  private readonly id: string;
  private name: string;
  private email: string;
  private orders: Order[] = []; // The 'one' side holds a collection
  
  constructor(id: string, name: string, email: string) {
    this.id = id;
    this.name = name;
    this.email = email;
  }
  
  getId(): string {
    return this.id;
  }
  
  // Expose immutable view of orders
  getOrders(): ReadonlyArray<Order> {
    return this.orders;
  }
  
  getOrderCount(): number {
    return this.orders.length;
  }
  
  // Factory method creates order with proper bidirectional link
  placeOrder(items: OrderItemData[]): Order {
    const order = new Order(this, items);
    this.orders.push(order);
    return order;
  }
  
  // Calculate aggregate across related entities
  getTotalSpent(): Money {
    return this.orders
      .filter(o => o.getStatus() === OrderStatus.COMPLETED)
      .reduce((sum, o) => sum.add(o.getTotal()), Money.ZERO);
  }
  
  // Internal method for order cancellation
  _removeOrder(order: Order): void {
    const index = this.orders.indexOf(order);
    if (index > -1) {
      this.orders.splice(index, 1);
    }
  }
}
 
class Order {
  private readonly id: string;
  private readonly customer: Customer; // The 'many' side holds a reference
  private readonly orderDate: Date;
  private status: OrderStatus;
  private items: OrderItem[] = [];
  
  constructor(customer: Customer, itemData: OrderItemData[]) {
    this.id = generateId();
    this.customer = customer;
    this.orderDate = new Date();
    this.status = OrderStatus.PENDING;
    
    // Create items from data
    for (const data of itemData) {
      this.items.push(new OrderItem(this, data.product, data.quantity));
    }
  }
  
  // Navigate to parent
  getCustomer(): Customer {
    return this.customer;
  }
  
  // Convenient accessor for customer ID without loading full customer
  getCustomerId(): string {
    return this.customer.getId();
  }
  
  getStatus(): OrderStatus {
    return this.status;
  }
  
  getTotal(): Money {
    return this.items.reduce(
      (sum, item) => sum.add(item.getSubtotal()),
      Money.ZERO
    );
  }
  
  cancel(): void {
    if (this.status !== OrderStatus.PENDING) {
      throw new Error("Only pending orders can be cancelled");
    }
    this.status = OrderStatus.CANCELLED;
    this.customer._removeOrder(this);
  }
}

Database Schema for One-to-Many:

The foreign key is always on the 'many' side, pointing to the 'one' side:

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
-- One-to-Many: Customer has many Orders
CREATE TABLE customers (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    customer_id UUID NOT NULL,  -- Foreign key on 'many' side
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT  -- Don't delete customer with orders
);
 
-- Index on foreign key for efficient joins
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
 
-- Efficient queries
-- Get customer's orders (follow 1:N)
SELECT * FROM orders WHERE customer_id = ? ORDER BY order_date DESC;
 
-- Get customer for an order (follow N:1)
SELECT c.* FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.id = ?;
 
-- Count orders per customer
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

The Collection Loading Problem

Many-to-Many Relationships

A many-to-many (M:N) relationship exists when multiple instances of Entity A can relate to multiple instances of Entity B, and vice versa.

Examples of many-to-many relationships:

Students ↔ Courses (students enroll in multiple courses; courses have multiple students)
Products ↔ Categories (products belong to multiple categories; categories contain multiple products)
Users ↔ Roles (users have multiple roles; roles are assigned to multiple users)
Authors ↔ Books (books can have multiple authors; authors write multiple books)
Actors ↔ Movies (actors appear in multiple movies; movies have multiple actors)

The Join Entity:

In code, this join table might be:

Implicit: The ORM handles it automatically, exposing just the two collections
Explicit: A first-class entity when the relationship itself has attributes

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
// Many-to-Many with NO additional attributes
// The relationship itself is simple - just a link
// Often handled implicitly by ORM
 
class Product {
  private readonly id: string;
  private name: string;
  private categories: Set<Category> = new Set(); // Simple M:N
  
  getCategories(): ReadonlySet<Category> {
    return this.categories;
  }
  
  addToCategory(category: Category): void {
    if (!this.categories.has(category)) {
      this.categories.add(category);
      category._addProduct(this); // Bidirectional sync
    }
  }
  
  removeFromCategory(category: Category): void {
    if (this.categories.delete(category)) {
      category._removeProduct(this); // Bidirectional sync
    }
  }
  
  _addCategory(category: Category): void {
    this.categories.add(category);
  }
  
  _removeCategory(category: Category): void {
    this.categories.delete(category);
  }
}
 
class Category {
  private readonly id: string;
  private name: string;
  private products: Set<Product> = new Set();
  
  getProducts(): ReadonlySet<Product> {
    return this.products;
  }
  
  addProduct(product: Product): void {
    if (!this.products.has(product)) {
      this.products.add(product);
      product._addCategory(this);
    }
  }
  
  _addProduct(product: Product): void {
    this.products.add(product);
  }
  
  _removeProduct(product: Product): void {
    this.products.delete(product);
  }
}

Many-to-Many with Relationship Attributes:

When the relationship itself carries data (enrollment date, role in movie, quantity in order), the join table becomes a first-class entity:

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
// Many-to-Many WITH relationship attributes
// The relationship is a first-class entity: Enrollment
 
class Student {
  private readonly id: string;
  private name: string;
  private enrollments: Enrollment[] = []; // Explicit join entities
  
  getEnrollments(): ReadonlyArray<Enrollment> {
    return this.enrollments;
  }
  
  // Navigate through join entity to get courses
  getCourses(): Course[] {
    return this.enrollments.map(e => e.getCourse());
  }
  
  enrollIn(course: Course, semester: Semester): Enrollment {
    // Check for duplicate enrollment
    const existing = this.enrollments.find(
      e => e.getCourse() === course && e.getSemester() === semester
    );
    if (existing) {
      throw new Error("Already enrolled in this course for this semester");
    }
    
    const enrollment = new Enrollment(this, course, semester);
    this.enrollments.push(enrollment);
    course._addEnrollment(enrollment);
    return enrollment;
  }
  
  _addEnrollment(enrollment: Enrollment): void {
    this.enrollments.push(enrollment);
  }
}
 
class Course {
  private readonly id: string;
  private name: string;
  private maxCapacity: number;
  private enrollments: Enrollment[] = [];
  
  getEnrollments(): ReadonlyArray<Enrollment> {
    return this.enrollments;
  }
  
  getStudents(): Student[] {
    return this.enrollments.map(e => e.getStudent());
  }
  
  getEnrollmentCount(): number {
    return this.enrollments.length;
  }
  
  hasCapacity(): boolean {
    return this.enrollments.length < this.maxCapacity;
  }
  
  _addEnrollment(enrollment: Enrollment): void {
    if (!this.hasCapacity()) {
      throw new Error("Course is at capacity");
    }
    this.enrollments.push(enrollment);
  }
}
 
// The JOIN ENTITY - represents the relationship with its own data
class Enrollment {
  private readonly id: string;
  private readonly student: Student;
  private readonly course: Course;
  private readonly semester: Semester;
  private readonly enrollmentDate: Date;
  private grade: Grade | null = null;
  private status: EnrollmentStatus;
  
  constructor(student: Student, course: Course, semester: Semester) {
    this.id = generateId();
    this.student = student;
    this.course = course;
    this.semester = semester;
    this.enrollmentDate = new Date();
    this.status = EnrollmentStatus.ACTIVE;
  }
  
  getStudent(): Student {
    return this.student;
  }
  
  getCourse(): Course {
    return this.course;
  }
  
  getSemester(): Semester {
    return this.semester;
  }
  
  assignGrade(grade: Grade): void {
    if (this.status !== EnrollmentStatus.ACTIVE) {
      throw new Error("Cannot grade dropped enrollment");
    }
    this.grade = grade;
    this.status = EnrollmentStatus.COMPLETED;
  }
  
  drop(): void {
    if (this.status !== EnrollmentStatus.ACTIVE) {
      throw new Error("Cannot drop non-active enrollment");
    }
    this.status = EnrollmentStatus.DROPPED;
  }
}

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
-- Many-to-Many: Students and Courses linked by Enrollments
CREATE TABLE students (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);
 
CREATE TABLE courses (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    max_capacity INTEGER NOT NULL DEFAULT 30
);
 
-- Join table with its own attributes
CREATE TABLE enrollments (
    id UUID PRIMARY KEY,
    student_id UUID NOT NULL,
    course_id UUID NOT NULL,
    semester VARCHAR(20) NOT NULL,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade VARCHAR(2),
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    
    -- Composite unique constraint: one enrollment per student/course/semester
    UNIQUE(student_id, course_id, semester)
);
 
-- Indexes for common query patterns
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_enrollments_semester ON enrollments(semester);
 
-- Query: Get all courses for a student
SELECT c.*, e.grade, e.status
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE e.student_id = ? AND e.semester = ?;
 
-- Query: Get all students in a course
SELECT s.*, e.grade, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.course_id = ? AND e.semester = ?;

When to Promote the Join Table

Relationship Types Compared

Let's consolidate the key differences between the three relationship types:

Relationship Type Comparison
Aspect	One-to-One (1:1)	One-to-Many (1:N)	Many-to-Many (M:N)
Frequency	Rare	Very common	Common
Foreign key location	Dependent side	'Many' side	Join table
Code representation (one side)	Single reference	Collection	Collection (often via join entity)
Code representation (other side)	Single reference (or none)	Single reference	Collection (often via join entity)
Typical loading strategy	Eager (usually small)	Lazy (possibly large)	Lazy (often large on both sides)
Cascade delete	Often cascade	Depends on ownership	Usually delete join records only
Primary use case	Separate concerns, optional data	Parent-child, ownership	Cross-cutting associations

Performance Considerations by Relationship Type

Each relationship type has distinct performance characteristics that affect query patterns, memory usage, and scalability.

One-to-One Performance

•Join cost: Single join is cheap; often loaded together via LEFT JOIN.
•Memory: Combined object size is predictable and bounded.
•Optimization: Can denormalize into single table if always accessed together.
•Risk: Over-separation leads to unnecessary joins. Only split if there's a genuine reason.

One-to-Many Performance

•Collection size variance: From 0 to millions. Must handle both extremes.
•The N+1 problem: Loading parent then lazy-loading each child = N+1 queries.
•Pagination essential: Never load unbounded collections. Always paginate or limit.
•Index critical: Foreign key column MUST be indexed for JOIN performance.
•Count vs load: Provide count() methods that don't load full collection.

Many-to-Many Performance

•Double join cost: Every traversal requires two joins (entity → join table → entity).
•Explosive combinations: M items × N items creates M×N join records. Can grow rapidly.
•Index both foreign keys: Join table needs indexes on both foreign key columns.
•Composite index for uniqueness: (entity_a_id, entity_b_id) composite index.
•Beware Cartesian products: Incorrect joins can explode row counts catastrophically.

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
// Performance-aware relationship access patterns
 
class Customer {
  private orders: Order[] | null = null; // Lazy, nullable
  private orderCount: number | null = null; // Cached count
  
  // DON'T: Load all orders just to count them
  // getOrderCount(): number {
  //   return this.getOrders().length; // Triggers full load!
  // }
  
  // DO: Separate count method that doesn't load collection
  async getOrderCount(): Promise<number> {
    if (this.orderCount === null) {
      this.orderCount = await this.orderRepository.countByCustomer(this.id);
    }
    return this.orderCount;
  }
  
  // DO: Paginated access
  async getOrdersPage(page: number, pageSize: number): Promise<Order[]> {
    return this.orderRepository.findByCustomer(this.id, {
      offset: page * pageSize,
      limit: pageSize,
      orderBy: 'orderDate DESC'
    });
  }
  
  // DO: Filtered access that limits results
  async getRecentOrders(limit: number = 10): Promise<Order[]> {
    return this.orderRepository.findByCustomer(this.id, {
      limit,
      orderBy: 'orderDate DESC'
    });
  }
  
  // DON'T: Expose raw collection that might be massive
  // getOrders(): Order[] { return this.orders; }
}

The N+1 Query Disaster

Navigation Patterns

How you traverse relationships affects both code clarity and performance. Different patterns suit different access needs.

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
// Pattern 1: Direct object navigation
// Natural OO style, but may trigger lazy loading
const customer = order.getCustomer();
const customerName = order.getCustomer().getName();
 
// Pattern 2: ID-only navigation (avoids loading)
// Use when you only need the ID for further queries
const customerId = order.getCustomerId(); // Just returns stored ID
const customerOrders = orderRepo.findByCustomerId(customerId);
 
// Pattern 3: Explicit loading control
// Clear about what's loaded and when
const order = await orderRepo.findById(orderId);
await order.loadCustomer(); // Explicit load
await order.loadItems();    // Explicit load
// Now safe to navigate
 
// Pattern 4: Eager loading at query time
// Best for known access patterns
const order = await orderRepo.findByIdWithDetails(orderId, {
  include: ['customer', 'items', 'items.product']
});
// All related data loaded in single query
 
// Pattern 5: Projection / DTO
// Return only needed data, skip entity materialization
interface OrderSummary {
  orderId: string;
  orderDate: Date;
  customerName: string;  // Flattened - no customer object
  totalAmount: number;
  itemCount: number;
}
 
const summary = await orderRepo.getOrderSummary(orderId);
// Single query, single result, no lazy loading concerns

Navigation Pattern Trade-offs
Pattern	When to Use	Pros	Cons
Direct navigation	Small graphs, known eager loaded	Natural OO style	Hidden lazy loads, N+1 risk
ID-only navigation	Cross-aggregate references	No unnecessary loads	Requires separate queries
Explicit loading	Complex operations on entities	Clear load timing	Verbose code
Eager loading	Known access patterns	Optimal query count	May over-fetch
Projection/DTO	Read-only, reporting, APIs	Minimal data transfer	No entity behavior

Common Mistakes in Relationship Modeling

Years of production experience reveal these recurring mistakes across teams and projects:

Mistakes to Avoid

•Modeling M:N as two 1:N relationships — If the relationship has no identity or attributes, use a proper M:N with implicit join table. Two 1:N relationships create orphan-able join records.
•Forcing 1:1 when 1:N would naturally fit — 'A User has one Address' seems clean until users need billing AND shipping addresses. Model for the actual domain, not perceived simplicity.
•Bidirectional when unidirectional suffices — Every bidirectional relationship doubles maintenance burden. Add back-references only when you genuinely need reverse navigation.
•Loading collections to check membership — Don't load 10,000 items to check if one exists. Use repository methods: exists(parentId, childId).
•Ignoring relationship cardinality changes — A 'User has one Department' becomes M:N when users can belong to multiple departments. Build flexibility for likely changes.
•Treating ORM magic as free — Lazy loading, dirty checking, and cascade operations have costs. Understand what your ORM generates before production.
•Cascading deletes without thought — Deleting a Customer that cascades to Orders that cascade to Payments... might delete financial records you're legally required to keep.

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
// ❌ MISTAKE: Loading collection to check membership
class Team {
  async hasMember(userId: string): Promise<boolean> {
    const members = await this.getMembers(); // Loads all members!
    return members.some(m => m.id === userId);
  }
}
 
// ✅ CORRECT: Repository method that checks directly
class Team {
  async hasMember(userId: string): Promise<boolean> {
    return this.membershipRepo.exists(this.id, userId);
    // SELECT EXISTS(SELECT 1 FROM memberships WHERE team_id = ? AND user_id = ?)
  }
}
 
// ❌ MISTAKE: Circular cascade
class Department {
  @OneToMany(() => Employee, { cascade: true })
  employees: Employee[];
}
class Employee {
  @ManyToOne(() => Department, { cascade: true }) // Danger!
  department: Department;
}
// Deleting either could cascade indefinitely
 
// ✅ CORRECT: One-way cascade matching ownership
class Department {
  @OneToMany(() => Employee, { cascade: ['insert', 'update'] })
  employees: Employee[];
}
class Employee {
  @ManyToOne(() => Department)  // No cascade back
  department: Department;
}

Summary: Relationship Types

Key Takeaways

•One-to-One relationships separate concerns—use them for optional data, security isolation, or performance optimization, not as default organization.
•One-to-Many is the workhorse relationship—understand it deeply, especially collection management, lazy loading, and the N+1 problem.
•Many-to-Many requires a join table—promote it to a first-class entity when the relationship carries any additional attributes.
•Foreign keys go on the 'many' side for 1:N, or in a join table for M:N.
•Performance varies dramatically by relationship type—always consider collection sizes and loading strategies.
•Navigation patterns should be explicit—don't hide lazy loading behind innocent-looking property access.
•Avoid common mistakes like bidirectionality by default, loading collections for membership checks, or thoughtless cascade deletes.

What's next:

Relationship Types Mastered

2 / 4