database-designamazon-dynamodbnosql

How to model Student/Teacher/Class with DynamoDb?

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

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:

enter image description here

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.

Solution

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

    Table Structure and Example Items

    1. Teacher

    2. Student

    3. Class

    4. Enrollment

    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.