DynamoDB表能否支持2个以上键？评估数据多维度查询咨询

Alright, let's walk through designing this DynamoDB table to perfectly fit your assessment data needs. I'll break this down step by step so it's easy to implement:

1. Primary Key Structure #

The key here is balancing efficient querying for user-specific data, assessment types, and multiple versions. Here's the optimal setup:

• Partition Key (Hash Key): UserID (String type)
  Use the AWS Cognito user ID directly. This groups all assessment records for a single user in the same partition, making it fast to fetch all their data at once.
• Sort Key (Range Key): AssessmentVersionKey (String type)
  Use a composite format: {AssessmentUniqueName}#{VersionIdentifier}. For example:
  • AnnualPerformanceReview#2024-05-15 (versioned by date)
  • TeamCultureSurvey#ScenarioA (versioned by scenario)
  • SkillAssessment#v3 (versioned by increment number)

This structure lets you:

• Fetch all assessments for a user by just specifying the UserID partition key
• Fetch all versions of a specific assessment for a user with a begins_with condition on the sort key
• Fetch a single specific version of an assessment by providing both the full partition and sort key

2. Core Attribute Definitions #

Include these attributes to keep your data structured and query-friendly:

• UserID: S (String) – Partition key (Cognito user ID)
• AssessmentVersionKey: S (String) – Sort key (composite assessment + version)
• AssessmentName: S (String) – Standalone assessment name (for easy filtering/projection)
• VersionIdentifier: S (String) – Standalone version value (date, scenario, etc.)
• Answers: M (Map) – Structured storage for assessment answers (map question IDs to responses)
• SubmittedAt: N (Number) – Unix timestamp of when the assessment was submitted (for sorting/filtering)
• Metadata: M (Map) – Optional: Store extra context like device type, session ID, or scenario details

3. Optional Secondary Index (for cross-user queries) #

If you ever need to query all users' responses for a specific assessment (e.g., run analytics on AnnualPerformanceReview), add a Global Secondary Index (GSI):

• GSI Partition Key: AssessmentName (String)
• GSI Sort Key: SubmittedAt (Number)

This lets you quickly pull all records for an assessment and sort them by submission time.

4. Example Table Item #

Here's how a real record might look in JSON format:

{
  "UserID": "cognito-user-789XYZ",
  "AssessmentVersionKey": "AnnualPerformanceReview#2024-05-15",
  "AssessmentName": "AnnualPerformanceReview",
  "VersionIdentifier": "2024-05-15",
  "Answers": {
    "q1": "Exceeds Expectations",
    "q2": "Led 3 cross-functional projects",
    "q3": 4.7
  },
  "SubmittedAt": 1715769600,
  "Metadata": {
    "ReviewCycle": "Mid-Year",
    "Device": "Desktop"
  }
}

5. Common Query Examples #

Fetch all assessments for a user #

// Java SDK v2 example
DynamoDbClient ddbClient = DynamoDbClient.create();

QueryRequest queryRequest = QueryRequest.builder()
    .tableName("UserAssessments")
    .keyConditionExpression("UserID = :userId")
    .expressionAttributeValues(Map.of(
        ":userId", AttributeValue.builder().s("cognito-user-789XYZ").build()
    ))
    .build();

QueryResponse response = ddbClient.query(queryRequest);

Fetch all versions of a specific assessment for a user #

QueryRequest queryRequest = QueryRequest.builder()
    .tableName("UserAssessments")
    .keyConditionExpression("UserID = :userId AND begins_with(AssessmentVersionKey, :assessmentPrefix)")
    .expressionAttributeValues(Map.of(
        ":userId", AttributeValue.builder().s("cognito-user-789XYZ").build(),
        ":assessmentPrefix", AttributeValue.builder().s("AnnualPerformanceReview#").build()
    ))
    .build();

内容的提问来源于stack exchange，提问作者jaimerr