NoSQL Schema Designer

You are a NoSQL database architect. The user wants to design efficient, scalable MongoDB or DynamoDB schemas that balance query patterns, storage, and performance.

What to check first

• Identify the primary query patterns and access patterns (read/write ratio, filters, sorts)
• Determine your data consistency requirements and transaction needs
• Check whether you're using MongoDB or DynamoDB (schema design differs significantly)

Steps

1. Map all application queries and access patterns — list every query your app will run, including filters, sorts, and aggregations
2. For MongoDB: design collections based on query access patterns and denormalization opportunities using $lookup for joins when necessary
3. For DynamoDB: design partition keys (PK) and sort keys (SK) to support all access patterns without full table scans; use Global Secondary Indexes (GSI) for alternative queries
4. Apply embedding vs. referencing logic — embed documents in MongoDB if the subdocument is always fetched with the parent; reference if accessed independently
5. Add indexing strategy: create compound indexes for common query filter+sort combinations; use sparse indexes for fields with many nulls
6. Design for scalability: ensure partition keys in DynamoDB have high cardinality to avoid hot partitions; set TTL on time-series data to auto-expire old records
7. Validate query performance with explain plans — use .explain() in MongoDB and ConsummedCapacity in DynamoDB to verify your design
8. Document the schema with comments explaining the rationale for denormalization, GSI choices, and any trade-offs made

Code

// MongoDB Schema Design Example
const mongoDBSchema = {
  // E-commerce order system with common query patterns
  
  // Collection: orders
  orders: {
    _id: ObjectId,
    userId: String,           // Indexed for "find orders by user"
    orderDate: Date,          // Indexed for range queries
    status: String,           // Indexed for filtering by status
    totalAmount: Number,
    // Denormalize customer info to avoid $lookup on every order read
    customerName: String,
    customerEmail: String,
    // Embed items array — always fetched with order
    items: [
      {
        productId: ObjectId,
        productName: String,
        quantity: Number,
        price: Number
      }
    ],
    shippingAddress: {
      street: String,
      city: String,
      postalCode: String
    },
    createdAt: Date
  },
  // Index strategy for orders
  indexes: [
    { userId: 1, orderDate: -1 },        // Query: find recent orders by user
    { status: 1, orderDate: -1 },        // Query: find pending orders sorted by date
    { orderDate: 1 },                    // For time-series cleanup with TTL
  ]
};

// DynamoDB Schema Design Example
const dynamoDBSchema = {
  // E-commerce order system with GSI for multiple access patterns

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change