How to model Student/Teacher/Class with DynamoDb?

Question

I'm modeling a DynamoDb schema, which currently supports three main entities: Teacher, Student and Class. Class entity has these relationships:

• A teacher has a class
• A student enrolls to a class

My access patterns are:

1. Find periods for student (Periods)
2. Find periods for teacher (Periods)
3. Get students by period (Students)
4. Get teacher by period (Teacher)

If the teacher wants to get the student list of a period, he will need to perform two queries (2, 3).

If a student wants to get the list of periods with their teacher, he will need to perform multiple queries (1, and several of 4).

Here my current dynamodb model:

All of this happens using a single table design and using their corresponding pk and sk. I have some of the lectures Alex Debrie, but I am debating if this is applicable to denormalize the data (in class and enrollment entities like student and teacher names). Or if it's acceptable to keep the design querying by multiple ids.

In terms of requirements, I supposing a class to contain a max of 50 students. A teacher may have 1-5 classes. I believe is fair to say a teacher or student rarely would update their names.

Note: I also read the following post, which an adjescency list for student and classes entities: How to model Student/Classes with DynamoDB (NoSQL)

Any advice on how to manage this class relationship with student and teacher in my design is well received. I feel this is getting over complicated in my design.

Answer 1

Given your requirements and access patterns, let's explore a more streamlined DynamoDB schema design using a single-table approach.

Entities and Access Patterns

Entities:

1. Teacher
2. Student
3. Class

Access Patterns:

1. Find periods for a student.
2. Find periods for a teacher.
3. Get students by period.
4. Get teacher by period.

• Partition Key (PK): A composite key based on entity type and unique identifiers.
• Sort Key (SK): A composite key to enable efficient querying.

Table Structure and Example Items

1. Teacher

• PK: TEACHER#<TeacherID>
• SK: #METADATA
• Attributes: TeacherName, OtherTeacherAttributes

2. Student

• PK: STUDENT#<StudentID>
• SK: #METADATA
• Attributes: StudentName, OtherStudentAttributes

3. Class

• PK: CLASS#<ClassID>
• SK: #METADATA
• Attributes: ClassName, TeacherID, Period

4. Enrollment

• PK: STUDENT#<StudentID>
• SK: ENROLLMENT#<ClassID>
• Attributes: ClassID, TeacherID, Period, ClassName, TeacherName

Handling Access Patterns

1. Find periods for a student:

   • Query: PK = STUDENT#<StudentID> AND begins_with(SK, 'ENROLLMENT#')
   • Result: Returns all enrollments for the student, which includes class details.

2. Find periods for a teacher:

   • Query: Create a Global Secondary Index (GSI) with:
     • GSI1PK: TEACHER#<TeacherID>
     • GSI1SK: Period
   • Query: GSI1PK = TEACHER#<TeacherID> AND GSI1SK = <Period>
   • Result: Returns all classes for the teacher in the specified period.

3. Get students by period:

   • Query: Create a GSI with:
     • GSI2PK: CLASS#<ClassID>
     • GSI2SK: ENROLLMENT#<StudentID>
   • Query: GSI2PK = CLASS#<ClassID>
   • Result: Returns all students enrolled in the class.

4. Get teacher by period:

   • Query: PK = CLASS#<ClassID> AND SK = #METADATA
   • Result: Returns the teacher information for the class.

Example Items

Teacher

{
  "PK": "TEACHER#T123",
  "SK": "#METADATA",
  "TeacherName": "Mr. Smith",
  "OtherTeacherAttributes": {...}
}

Student

{
  "PK": "STUDENT#S456",
  "SK": "#METADATA",
  "StudentName": "Alice Johnson",
  "OtherStudentAttributes": {...}
}

Class

{
  "PK": "CLASS#C789",
  "SK": "#METADATA",
  "ClassName": "Math 101",
  "TeacherID": "T123",
  "Period": "2024-07-21T09:00:00Z"
}

Enrollment

{
  "PK": "STUDENT#S456",
  "SK": "ENROLLMENT#C789",
  "ClassID": "C789",
  "TeacherID": "T123",
  "Period": "2024-07-21T09:00:00Z",
  "ClassName": "Math 101",
  "TeacherName": "Mr. Smith"
}

Denormalization Considerations

In DynamoDB, denormalization is often beneficial for reducing the number of queries. Since updates to teacher and student names are rare, duplicating this information in enrollment records is acceptable and will improve query efficiency.