DynamoDB选型咨询：书评站点哈希/排序键及索引方案（含AppSync）

Hey there! Let's work through designing your DynamoDB table and indexes for your book review site, tailored to your GraphQL Book type and the common access patterns you'll likely need.

Core Table Design (Main Table) #

First, let's anchor the main table to your most critical access pattern: fetching a single book by its ISBN (since ISBN is a unique identifier for books, this is non-negotiable).

• Hash Key (PK): isbn (Int!) – This uniquely identifies each book, making single-book lookups fast and efficient.
• Sort Key (SK): Use a fixed string like #BOOK – This helps if you later adopt a single-table design (to separate book records from other entities like reviews or user data) while keeping the core book data clean right now.
• Attributes to Store: All fields from your Book type: year, title, description, front_cover_photo_url, genre_ids, count_thumbs, us_release_date.

Here's how a sample record would look:

{
  "PK": 9781234567890,
  "SK": "#BOOK",
  "year": 2023,
  "title": "The Great Book Review",
  "description": "A deep dive into book reviewing culture...",
  "front_cover_photo_url": "https://example.com/cover.jpg",
  "genre_ids": [1, 3, 5],
  "count_thumbs": 42,
  "us_release_date": "2023-09-15"
}

GSI/LSI Design (Matching Your Access Patterns) #

LSIs (Local Secondary Indexes) only make sense if you have multiple records under the same PK – since our PK is a unique ISBN, LSIs won't add value here. We'll focus on GSIs (Global Secondary Indexes) to support other key queries:

1. Query Books by Year (with optional sorting by popularity) #

If users want to browse books from a specific year (e.g., "Show me all 2023 books, sorted by most liked"), create this GSI:

• GSI1 PK: year (Int)
• GSI1 SK: -count_thumbs (Int – store as negative to get descending sort by popularity, since DynamoDB defaults to ascending)
• Projected Attributes: isbn, title, front_cover_photo_url, count_thumbs (only include what you need for the browse view to save cost/storage)

2. Query Books by Genre (sorted by popularity) #

Since your genre_ids is an array, you can't query directly by array elements. Instead, we'll de-normalize: for each genre a book belongs to, write a separate entry to this GSI.

• GSI2 PK: genre_id (Int)
• GSI2 SK: -count_thumbs (Int)
• Projected Attributes: isbn, title, front_cover_photo_url, count_thumbs

Note: In your AppSync resolver, you'll need to loop through the genre_ids array and write one GSI entry per genre for each book.

3. Global "Most Popular" Books #

For a homepage carousel of top-rated books:

• GSI3 PK: #GLOBAL_HOT (fixed string)
• GSI3 SK: -count_thumbs (Int)
• Projected Attributes: isbn, title, front_cover_photo_url, count_thumbs

4. Upcoming/Recently Released Books #

To surface books that are soon to launch or just came out:

• GSI4 PK: release_status (String – use values like UPCOMING or RELEASED, determined by comparing us_release_date to the current date)
• GSI4 SK: us_release_date (String – use ISO format YYYY-MM-DD for proper date sorting)
• Projected Attributes: isbn, title, front_cover_photo_url, us_release_date

Key Considerations #

• De-normalization: This is normal in DynamoDB – duplicating data (like genre entries) is worth it for fast, scalable queries.
• AppSync Integration: Use VTL resolvers to handle writing to both the main table and all relevant GSIs when a book is created/updated.
• Cost Optimization: Only project necessary attributes in GSIs to reduce storage and read/write costs.

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