Modeling Relationships

In a relational database, you model relationships using Foreign Keys and Join tables. In DynamoDB, we use Item Collections and Adjacency Lists.

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

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

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, sorted by date (because of the SK).

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.

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 questions:

• “What courses is Alice taking?” (Query Base Table)
• “Who is taking Math?” (Query GSI)

---

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.

// 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);
}

// 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.