Access-Pattern-Driven Design

The Two Directions of Design

 ENTITY-FIRST (relational instinct):
   model objects → normalize → answer queries as best you can

 QUERY-FIRST (access-pattern-driven):
   list the QUERIES → design storage so each is cheap

 relational databases forgive entity-first thinking
 (ad-hoc SQL rescues you). NoSQL stores DO NOT:
 Cassandra/DynamoDB have no rescue query —
 if the access pattern wasn't designed in, it's a full scan.

The Method: Query Inventory First

 before any schema, write the ACCESS PATTERN TABLE:

 #  QUERY                                FREQ    LATENCY NEED
 1  get trip by id                       high     p99<10ms
 2  rider's trips, newest first          high     p99<50ms
 3  driver's current trip                very high p99<5ms
 4  city's active drivers (map view)     extreme  p99<20ms
 5  monthly revenue by city              low      seconds OK
 6  fraud check: rider's recent payments medium  p99<100ms

 every row becomes a DESIGN OBLIGATION.
 frequency × latency sets how expensive optimization is justified.

Mapping Patterns to Structures

 DynamoDB-style single-table example:

 #1  PK=TRIP#<id>                    direct GetItem ✓
 #2  PK=USER#<rider>, SK=TRIP#<ts>   range query newest-first ✓
 #3  PK=DRIVER#<id>, SK=STATE        single read ✓
 #4  GSI: PK=CITY#<city>, SK=STATUS  index for map feed ✓
 #5  → NOT DynamoDB. warehouse handles analytics. ✗ honest!
 #6  PK=USER#<rider>, SK=PAY#<ts>    bounded window scan ✓

 note #5: access-pattern discipline includes admitting
 which patterns belong in DIFFERENT storage entirely.

The Rules That Emerge

 □ one table per distinct PARTITION shape (not per entity!)
 □ composite keys encode hierarchy: USER#912 / TRIP#2026...
 □ GSIs cost money on EVERY write — budget them like indexes²
 □ hot partition keys need bucketing planned upfront
 □ item size limits (~400KB) bound embedding decisions
 □ "get all X" queries are a smell — paginate by design

Relational Benefits From the Same Discipline

 even with SQL, query-first thinking improves outcomes:

 - drives COMPOSITE INDEX design ((rider_id, created_at DESC))
 - reveals which tables need read replicas vs cache coverage  
 - identifies analytics queries to route to warehouse early
 - sizes connection pools per endpoint class honestly

 the discipline transfers; only the enforcement differs
 (SQL lets you cheat later; DynamoDB doesn't).

The Anti-Patterns

 ✗ modeling entities then discovering queries need scans
 ✗ one-table-per-entity in DynamoDB (misses the entire point)
 ✗ GSIs added reactively until write throughput dies
 ✗ storing data no listed pattern ever reads ("just in case")
 
 each costs real money/latency at scale; all trace back to
 skipping the inventory table exercise.

Interview Framing

This lesson IS the interview skill: strong candidates open data-model discussions with “let me list the access patterns” and build a visible table. That single habit — querying requirements before naming storage — signals seniority more reliably than any specific technology choice. Practice making the table explicit even when thinking silently.