DynamoDB Single-Table Design: Building a Many-to-Many Relationship Model Using Adjacency Lists

A many-to-many adjacency list in DynamoDB is a data modeling pattern used to represent relationships where multiple entities can be associated with multiple other entities.

Let's consider a student-courses example: A student can enroll in multiple courses. A course can have multiple students. The adjacency list pattern in DynamoDB typically involves:

Storing different types of entities in the same table.

Using a composite primary key to distinguish between entity types and relationships.

Using a Global Secondary Index (GSI) to enable efficient querying in both directions.

Here is an example of a many-to-many relationship between Students and Courses using an adjacency list pattern in DynamoDB.

Table Design

Table Name: Education

PK: Either STUDENT#<studentId> or COURSE#<courseId>

SK: Either META (for entity details) or relationship type with ID

GSI1-PK: SK (for reverse lookups)

GSI1-SK: PK

Example Data Model:

{ TableName: "Education", KeySchema: [ { AttributeName: "PK", KeyType: "HASH" }, { AttributeName: "SK", KeyType: "RANGE" } ], GlobalSecondaryIndexes: [{ IndexName: "GSI1", KeySchema: [ { AttributeName: "GSI1-PK", KeyType: "HASH" }, { AttributeName: "GSI1-SK", KeyType: "RANGE" } ] } ] }

Sample Data and Access Patterns:

Let's break down the data records in the DynamoDB table using visual examples:

1.Base Records (Metadata):

Student Record

{ "PK": "STUDENT#S1", # Partition Key "SK": "METADATA", # Sort Key "EntityType": "STUDENT", # Helps in filtering "Name": "John Doe", "Email": "john@example.com", "YearLevel": 3 }

Course Record

{ "PK": "COURSE#C1", # Partition Key "SK": "METADATA", # Sort Key "EntityType": "COURSE", # Helps in filtering "Name": "Advanced Mathematics", "Professor": "Dr. Smith", "Credits": 3 }

2. Relationship Records (Enrollments):

Student-Course Enrollment Record

{ "PK": "STUDENT#S1", # Partition Key "SK": "COURSE#C1", # Sort Key "GSI1-PK": "COURSE#C1", # For reverse lookup "GSI1-SK": "STUDENT#S1", # For reverse lookup "EntityType": "ENROLLMENT", "EnrollmentDate": "2024-03-31T10:00:00", "Grade": "A" }

Let's see how this looks with multiple records:

Example of all records for two students and two courses

Metadata Records

** Student Records**

[ {
    "PK": "STUDENT#S1",
    "SK": "METADATA",
    "EntityType": "STUDENT",
    "Name": "John Doe",
    "Email": "john@example.com",
    "YearLevel": 3
},
{
    "PK": "STUDENT#S2",
    "SK": "METADATA",
    "EntityType": "STUDENT",
    "Name": "Jane Smith",
    "Email": "jane@example.com",
    "YearLevel": 2
 } ]

Course Records

{
    "PK": "COURSE#C1",
    "SK": "METADATA",
    "EntityType": "COURSE",
    "Name": "Advanced Mathematics",
    "Professor": "Dr. Smith",
    "Credits": 3
},
{
    "PK": "COURSE#C2",
    "SK": "METADATA",
    "EntityType": "COURSE",
    "Name": "Physics 101",
    "Professor": "Dr. Johnson",
    "Credits": 4
},

Enrollment Records

[{
    "PK": "STUDENT#S1",
    "SK": "COURSE#C1",
    "GSI1-PK": "COURSE#C1",
    "GSI1-SK": "STUDENT#S1",
    "EntityType": "ENROLLMENT",
    "EnrollmentDate": "2024-03-31T10:00:00",
    "Grade": "A"
},
{
    "PK": "STUDENT#S1",
    "SK": "COURSE#C2",
    "GSI1-PK": "COURSE#C2",
    "GSI1-SK": "STUDENT#S1",
    "EntityType": "ENROLLMENT",
    "EnrollmentDate": "2024-03-31T11:00:00",
    "Grade": "B+"
},
{
    "PK": "STUDENT#S2",
    "SK": "COURSE#C1",
    "GSI1-PK": "COURSE#C1",
    "GSI1-SK": "STUDENT#S2",
    "EntityType": "ENROLLMENT",
    "EnrollmentDate": "2024-03-31T09:00:00",
    "Grade": "A-"
} ]

Key Query Patterns:

1. Get Student Details:

Query by PK="STUDENT#S1" and SK="METADATA"

response = table.get_item( Key={ 'PK': 'STUDENT#S1', 'SK': 'METADATA' } )

2. Get All Courses for Student:

Query by PK="STUDENT#S1" and SK begins_with "COURSE#"

response = table.query( KeyConditionExpression=Key('PK').eq('STUDENT#S1') & Key('SK').begins_with('COURSE#') )

3. Get All Students in Course (using GSI):

Query GSI1 where GSI1-PK="COURSE#C1"

response = table.query( IndexName='GSI1', KeyConditionExpression=Key('GSI1-PK').eq('COURSE#C1') )

Access Pattern Examples:

1. Find all John's courses:

Query PK="STUDENT#S1": Results:

• COURSE#C1 (Advanced Mathematics, Grade: A)
• COURSE#C2 (Physics 101, Grade: B+)

2. Find all students in Advanced Mathematics:

Query GSI1-PK="COURSE#C1":

Results:

• STUDENT#S1 (John Doe, Grade: A)
• STUDENT#S2 (Jane Smith, Grade: A-)

The benefits of the above design are - it allows us to:

• Efficiently find all courses a student is taking
• Efficiently find all students in a course
• Store additional relationship data (grades, dates)
• Maintain entity details in a single place
• Scale effectively with DynamoDB's partition model

The key aspects of adjacency list data modeling in Dynamodb is that - the table involves:

• Single table design with different entity types
• Use of composite keys (PK/SK) to organize data
• GSI for efficient reverse lookups
• Flexible schema for additional attributes
• Efficient queries in both directions
• Minimum data duplication for entity details