Modeling Relationships

In a relational database, you model relationships using Foreign Keys and Join tables. When a student enrolls in a course, you insert a row in an enrollments table connecting student_id to course_id. To read the data, the database performs a CPU-heavy JOIN operation at query time.

In DynamoDB, JOINs do not exist. We must pre-join our data. We model relationships using Item Collections and Adjacency Lists.

1. One-to-Many (1:N)

The most common relationship (e.g., A User has many Orders, a Department has many Employees).

The Strategy: Item Collections

We store the “One” (Parent) and the “Many” (Children) in the same partition by sharing the Partition Key. We distinguish them using the Sort Key.

  • Parent (User): PK: USER#123, SK: METADATA
  • Child (Order 1): PK: USER#123, SK: ORDER#2023-01-01
  • Child (Order 2): PK: USER#123, SK: ORDER#2023-01-05

Querying

To get the User and all their Orders, you perform a single Query operation: PK = "USER#123"

This retrieves the entire collection in one go, natively sorted by date (because the SK acts as an inherent B-tree index).

2. Many-to-Many (M:N)

Example: Students and Courses. A Student can take many Courses; a Course can have many Students.

The Strategy: Adjacency Lists

We treat the relationship as a “Link” item, effectively modeling a graph structure within DynamoDB.

  1. Base Table: Store the link from the Student’s perspective.
    • PK: STUDENT#Alice, SK: COURSE#Math
  2. Global Secondary Index (GSI): Invert the keys to see it from the Course’s perspective.
    • GSI_PK: COURSE#Math, GSI_SK: STUDENT#Alice

This allows you to answer both access patterns effortlessly:

  • Access Pattern 1: “What courses is Alice taking?” → Query Base Table: PK = STUDENT#Alice
  • Access Pattern 2: “Who is taking Math?” → Query GSI: GSI_PK = COURSE#Math

Note on Edge Cases: If a course has millions of students, the GSI partition for that course (GSI_PK = COURSE#Math) might suffer from a hot partition. In extreme cases, you may need to implement write sharding, but for most standard relationships, Adjacency Lists are the gold standard.

3. Interactive: Adjacency List Simulator

Toggle between the Base Table (Student View) and the GSI (Course View) to see how data is effectively “pivoted”.

Base Table (By Student)

PK (Partition Key) SK (Sort Key) Attributes

4. Code Implementation

How to model these relationships in code.

Java

// Defining the Inverted Index (GSI)
@DynamoDbSecondaryPartitionKey(indexNames = "GSI1")
@DynamoDbAttribute("GSI1PK")
public String getGsi1Pk() { return gsi1Pk; }

@DynamoDbSecondarySortKey(indexNames = "GSI1")
@DynamoDbAttribute("GSI1SK")
public String getGsi1Sk() { return gsi1Sk; }

// Creating a Relationship Item
public void createEnrollment(String studentId, String courseId) {
  SingleTableItem item = new SingleTableItem();
  item.setPk("STUDENT#" + studentId);
  item.setSk("COURSE#" + courseId);

  // Automatic GSI population (handled by DynamoDB if attributes are set)
  item.setGsi1Pk("COURSE#" + courseId);
  item.setGsi1Sk("STUDENT#" + studentId);

  table.putItem(item);
}

Go

// Enrollment Item with GSI tags
type Enrollment struct {
  PK      string `dynamodbav:"PK"`      // STUDENT#Alice
  SK      string `dynamodbav:"SK"`      // COURSE#Math
  GSI1PK  string `dynamodbav:"GSI1PK"`  // COURSE#Math
  GSI1SK  string `dynamodbav:"GSI1SK"`  // STUDENT#Alice
  Grade   string `dynamodbav:"Grade"`
}

func CreateEnrollment(student, course string) Enrollment {
  return Enrollment{
    PK:     "STUDENT#" + student,
    SK:     "COURSE#" + course,
    GSI1PK: "COURSE#" + course,
    GSI1SK: "STUDENT#" + student,
    Grade:  "A",
  }
}

5. Summary

  • 1:N: Use Item Collections (Same PK, different SKs).
  • M:N: Use Adjacency Lists (Link Items) + Global Secondary Index (GSI) to “invert” the relationship.
  • Access Patterns: The structure of your data is entirely dependent on the questions you need to ask it.