DynamoDB单表设计：按类别查询当前有效活跃问卷的实现咨询

Hey there! Let's work through this DynamoDB problem for your survey management system. First, let's unpack your current setup, then refine the data model to make your target query efficient and straightforward.

1. What's Missing in Your Current Model #

Your current approach of storing two records per survey creates redundancy (you have to update two entries if dates or status change) and the concatenated data_attr value (1564012800-1564876799) can't be used for proper range queries on dates—DynamoDB can't parse that string to compare against the current timestamp. Let's fix that.

2. Optimized Data Model #

Since your core query is: "Get all Active surveys in a specific Category where the current date falls between start_date and end_date", we'll design a table and Global Secondary Index (GSI) to directly support this without workarounds.

Main Table (tbl_surveys) #

Keep a single record per survey (no duplicates!) with these attributes:

• tbl_pk_surv: Partition Key (e.g., Surv-0tOrClRnTz — unique per survey)
• tbl_sk_surv: Sort Key (set to SURVEY for all survey records, as you did before)
• survey_name: Name of the survey (e.g., Survey1)
• cat_name: Category name (e.g., Cat1)
• start_date: Unix timestamp (seconds/milliseconds, be consistent) for when the survey starts
• end_date: Unix timestamp for when the survey ends
• status: Numeric flag (1 = Active, 0 = Inactive)

GSI for Target Query #

Create a GSI tailored to your core query needs:

• GSI Partition Key: cat_name (lets us instantly target all surveys in a specific category)
• GSI Sort Key: start_date (lets us filter out surveys that haven't started yet)
• Projected Attributes: Include end_date, status, survey_name (so we don't need to "backtrack" to the main table for these fields—saves time and cost)

Name this GSI something like GSI_CatName_StartDate for clarity.

3. Query Implementation #

Now, let's write a query that pulls exactly what you need. We'll use Python's boto3 as an example, but the logic translates to other SDKs too.

First, get the current Unix timestamp (match the precision you use for start_date/end_date):

import boto3
import time

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('tbl_surveys')

# Get current timestamp (use int(time.time() * 1000) for milliseconds)
current_ts = int(time.time())
target_category = "Cat1"
active_status = 1

Then run the query against the GSI:

response = table.query(
    IndexName='GSI_CatName_StartDate',
    KeyConditionExpression='cat_name = :cat AND start_date <= :current',
    FilterExpression='end_date >= :current AND status = :active',
    ExpressionAttributeValues={
        ':cat': target_category,
        ':current': current_ts,
        ':active': active_status
    },
    ProjectionExpression='survey_name, start_date, end_date'  # Return only fields you need
)

# Process results
for survey in response['Items']:
    print(f"Active Survey: {survey['survey_name']} | Runs until: {survey['end_date']}")

How This Works #

1. Key Condition: We first target all surveys in Cat1 where start_date is <= current time (surveys that have already started).
2. Filter Condition: From that subset, we keep only surveys where end_date is >= current time (still running) and status is Active.
3. Projection: We only request the fields we need to minimize data transfer.

4. Key Notes #

• Avoid Redundancy: Ditching the duplicate record eliminates the risk of data inconsistency (e.g., updating end_date in one record but not the other).
• GSI Projection: Including all needed fields in the GSI avoids "fetching from the main table" (a.k.a. "backfilling"), which slows down queries and increases costs.
• Timestamp Consistency: Stick to either seconds or milliseconds for all timestamps—mixing them will break your date comparisons.

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