The Database Design Crunch

You're designing a new system. You need a data model. Do you normalize everything? Do you denormalize for performance? Do you use a relational database or NoSQL? How do you structure your tables? How do you avoid the n+1 query problem before it happens?

The database decisions you make at design time compound for years. A schema that's poorly normalized becomes a maintenance nightmare. One that's over-normalized with 15 joins on every query becomes a performance nightmare. Get it wrong and you're either constantly fixing queries or constantly refactoring the schema.

This is where AI becomes invaluable. Not as a source of design truth, but as a thinking partner who's seen thousands of data models and can spot patterns you might miss. The best engineering teams use AI not to avoid thinking about schema design, but to think about it more systematically and catch edge cases before they reach production.

What AI is Good At (and What It Misses)

• Suggesting efficient schema structures for standard patterns
  - Identifying normalization problems that span multiple tables
  - Spotting n+1 query patterns before they happen in ORM code
  - Recommending indexes based on access patterns you describe
  - Evaluating trade-offs between normalization and performance with reasoning
  - Handling historical data versioning strategies and temporal schemas
  - Explaining why a particular design choice matters (with caveats)

What it's genuinely not good at:

• Understanding your unique, proprietary access patterns without explicit detail
  - Predicting future feature requests you haven't articulated yet
  - Evaluating cost per query when you have million-row tables and specific cloud pricing
  - Making the final architectural call when trade-offs truly are ambiguous
  - Understanding legacy constraints in your existing system (if migrating)

The working model: You bring domain knowledge and access patterns. The AI suggests efficient implementations and asks clarifying questions you might not have thought to ask. Together you arrive at better decisions than either alone. AI as sanity-checker, not oracle.

The Data Design Process: Describe what you're storing and how you'll access it. The AI suggests a schema. You evaluate it against your requirements. Iterate.

The Schema Design Workflow

Step 1: Describe Your Domain

Start with what you're storing and what you need to do with it:

"I'm building an e-commerce platform. I need to store:
- Products (name, description, price, inventory)
- Users (name, email, address, payment methods)
- Orders (user, items, total, status, timestamps)
- Inventory (which products at which locations, stock levels)
- Reviews (user, product, rating, text)

Access patterns:
- Find all products in a category
- Show order history for a user
- Check if a product is in stock at a location
- List reviews for a product with average rating
- Update inventory after an order

Scale: 10M products, 100M users, 1B orders annually, 10K requests/second."

Step 2: Ask for a Schema

"Design a PostgreSQL schema for this. Optimize for the access patterns above. Include indexes, primary/foreign keys, and constraints. Include your reasoning."

Step 3: Evaluate the Proposal

The AI generates a schema. Review it for:

• Does it support all the access patterns you mentioned?
  - Is it efficiently indexed?
  - Would queries be fast?
  - Is it easy to maintain and extend?

Usually it's good. Sometimes you'll question choices: "Why store addresses denormalized instead of in a separate table?" This pushes you to understand the trade-offs.

Step 4: Dive Into Specifics

For any part you're uncertain about, dig deeper:

"Let's focus on the orders table. How would you handle:
- Orders that span multiple locations (warehouse selection)?
- Inventory reserved during checkout but not yet paid?
- Order amendments (what happens if something goes out of stock)?
- Refunds and returns?"

The AI walks you through the edge cases, helping you design a schema that handles them correctly.

Query Optimization and Index Design

A good schema is only half the battle. Queries need to be fast.

Pattern 1: Index Recommendations

"Here's my schema:
[schema]

Here are my most common queries:
[list queries]

What indexes should I create to make these fast? What else might I be missing?"

The AI suggests indexes and explains which queries they optimize. You can usually trust these recommendations, the AI can see patterns in query structure.

Pattern 2: Query Analysis

"This query is slow:
[paste slow query]

The schema is:
[schema]

Why is this slow? How would you rewrite it?"

The AI explains the problem (missing index, bad join order, unnecessary table scans) and suggests a fix. You test it. If faster, adopt it.

Pattern 3: N+1 Query Prevention

"I'm building a feature that shows a user's orders with all items in each order. Here's my ORM code:

[code]

This will have an n+1 query problem, won't it? How should I restructure this?"

The AI confirms the problem and suggests eager loading or batch loading patterns. You implement them. Problem solved before it reaches production.

Normalization vs. Denormalization Trade-offs

One of the hardest decisions in schema design: when to denormalize for performance.

The Denormalization Question

"For products, should I store:
1. Category in product table (denormalized, fast queries, harder to maintain if categories change)
2. Separate category table (normalized, slower queries because of joins, easier to maintain)

Our access pattern is: show product details with category. We have 10K products, 100K categories. We need to list products by category (which requires filtering)."

AI's response: "Keep separate tables. Joins are fast enough for your scale. Denormalization isn't necessary until your category list is very large or your join is expensive. Separate tables make it easy to rename or modify categories without touching the products table."

This prevents premature optimization and maintains flexibility.

Real Denormalization Example

"For orders, should I store the current product prices in the order line items, or look them up from the products table when needed? Prices change over time, and users expect to see what they actually paid."

AI's response: "Store prices in order line items. This denormalization is correct because:
1. Historical accuracy: users need to see what they paid, not current prices
2. Audit trail: price changes should be traceable
3. Performance: don't want to look up thousands of products for an order history page
This is good denormalization because it solves a real problem and is unlikely to cause maintenance issues."

Key Insight

The goal isn't perfect normalization or maximum denormalization. It's a schema that supports your access patterns, is maintainable, and performs well at your scale. AI helps you find that balance by thinking through trade-offs.

Evolving Schemas Over Time

Schemas need to change as your system grows. The challenge is doing this without destroying existing data or causing downtime.

Pattern: Schema Evolution Planning

"We want to add a feature that tracks product views per user. We need to store:
- Which user viewed which product
- When (timestamp)
- How many times

We have 100M users and 10M products. Users view ~20 products per session, ~5 sessions daily.

How should I structure this data? Should it be in the main database or somewhere else?"

AI's response: "This is high volume (100M × 20 × 5 = 10B events daily). Store this in a time-series database or data warehouse, not your main transactional database. Your main database schema stays clean. You get better performance and simpler backups."

This prevents architectural mistakes that are hard to fix later.

Database Choice: SQL vs. NoSQL

When you're deciding between relational and non-relational databases, ask the AI to think through your requirements:

"We're considering whether to use PostgreSQL or MongoDB for a content platform. We store:
- Posts (text, author, timestamps, tags)
- Comments (post, author, text, timestamps)
- User preferences (flexible schema, varies by user)

Access patterns:
- Find recent posts by tag
- Show comments on a post
- Query user preferences by user ID

Scale: 100M posts, 1B comments, 100M users.

Should we use SQL or NoSQL? What are the trade-offs?"

AI will likely suggest: "Postgres for posts and comments (relational data, clear schema, complex queries). Maybe a document store for user preferences if the schema really varies significantly. But you can also store preferences as JSON in Postgres and get the best of both worlds."

This prevents choosing a database based on hype rather than actual requirements.

When This Goes Wrong: Common Failure Modes

Failure Mode 1: The Over-Engineered Schema

You ask AI for a schema. It proposes something with 8 tables, computed columns, and constraints everywhere. Beautiful design. Impossible to modify. You spend 3 months on migrations instead of features.

Antidote: Ask the AI not just for a schema but for a migration path. Ask what would be expensive to change later. If the answer is "almost everything," that's a red flag. Good schemas have flexibility built in.

Failure Mode 2: The Missing Access Pattern

You describe your access patterns. AI designs around them. Six months in production, Product wants a new report: "Show me top customers by revenue, filtered by signup date." Your schema can't do this efficiently. You need a new index or a denormalized column.

Antidote: When asking AI for schema design, don't just list current access patterns. Ask: "What other access patterns might we need in the next 12 months?" Have AI think forward. Get explicit confirmation from Product that you're not missing anything obvious.

Failure Mode 3: Mistaking Performance Testing for Production Testing

Your schema performs great in testing with synthetic data. In production with real data, it's slow. The test data didn't have the distribution of real data. Indexes that should work don't. Joins that seemed fast lock up.

Antidote: Test schema with realistic data volume and distribution. Use production-like load. Don't trust AI recommendations without production testing.

Real Case Study: E-Commerce Platform Schema Evolution

A mid-size e-commerce company (Series B) built their initial schema in 2022 without AI thinking. By 2024, they had orders, products, inventory, and reviews. Schema worked, but adding new features was slow. They asked AI to review and suggest improvements.

AI identified: (1) inventory was denormalized across three tables (hard to keep consistent), (2) no time-series data for trending products (had to calculate on the fly), (3) reviews couldn't handle variants efficiently (all reviews for SKU, not for specific variant).

Over 6 weeks, guided by AI-suggested designs, they refactored: (1) centralized inventory with audit trail, (2) added product_metrics (pre-calculated trends), (3) split reviews by variant. New feature launch: 2-week sprint instead of 2-month project. Cost of refactoring: paid for itself in one quarter through faster shipping.

What to Do Monday Morning

• For a system you're building soon, describe the domain and access patterns to the AI in detail. Don't just list them. Explain why each access pattern matters. Ask for a schema design and explicitly ask: "What access patterns would be expensive to add later?"
  - Take an existing system with a schema you wrote a year ago. Ask the AI: "Review this schema. Does it make sense? What would be expensive to change? Are there obvious improvements?" Document the gaps between original design and what you've learned. This teaches you what you might do differently now.
  - For one of your slow queries, ask the AI to analyze it. But don't just implement the suggestion blindly. Ask: "Why is this slow? Are there three ways to fix this? What are the trade-offs?" Test before deploying. Measure improvement.
  - Create a schema design checklist. Have all access patterns been addressed? Are they documented with rationale? Are indexes efficient? Is normalization appropriate? Does the schema have room to grow? Use it for the next three designs, then refine based on what you learn.

FAQ

Q: Should I trust AI schema designs?

A: For standard OLTP patterns (relational, CRUD-heavy, clear access patterns), trust it at ~80%. For unusual requirements, data warehouse patterns, or highly specialized use cases, treat it as a starting point, not gospel. Always review the proposal. Test queries with realistic data and load before deploying. Never trust a schema design that hasn't been vetted by someone who understands your specific domain constraints.

Q: What if AI's schema conflicts with my team's strong opinions?

A: That's actually valuable. If your team disagrees with the AI suggestion, ask both the AI and your team to defend their positions with specifics: "What access patterns does each design optimize for? What does each design make expensive?" This forces clarity. Usually one design will win on merits once you see the tradeoffs explicitly.

Q: How do I know if my schema will scale?

A: Ask the AI: "At 100M rows and 10k req/s, would these queries still be fast? What could cause problems?" It can spot obvious issues like missing indexes or N-way joins. But actually test under production-like load with realistic data distribution, that's the only real validation. Theory and practice diverge frequently with schemas.

Q: When should I denormalize?

A: Only when you have a specific, measured performance problem that denormalization solves. Never preemptively. Keep schemas normalized unless you have data showing normalization is the bottleneck. Common case: pre-calculated totals in order headers (sum of items). Rare case: storing addresses denormalized because they're immutable and lookups are expensive. If you're not sure, stay normalized.

Q: How do I change a schema without downtime?

A: This is one area where AI excels. Describe your current schema and target schema, plus constraints (system must run during migration, maximum downtime = 30 seconds). Ask the AI: "Give me a migration plan that works while the system is running." It will suggest: add column, backfill data in background jobs, switch to new column, drop old column. This works. Always have a rollback plan.

Q: Can I use AI to generate migrations?

A: Yes, after you've planned the change. "Here's the schema change I need to make. Write a [PostgreSQL/MySQL] migration that handles edge cases and won't lock tables for more than a few seconds." AI-generated migrations are often good but need human review. Watch out for: missing constraints, incorrect data type conversions, performance implications. Always test in staging with production-like data size before running in production. Never trust a migration you haven't personally reviewed and tested.

On This Page

Watch the Lecture
The Database Design Crunch
The Schema Design Workflow
Query Optimization and Index Design
Normalization vs. Denormalization
Evolving Schemas Over Time
Database Choice: SQL vs. NoSQL
Failure Modes
Case Study
Monday Morning Action
FAQ

Chapter Details

Part of