principle-data-modelinglisted

<!-- preload-canary: SWB-PRELOAD-PRINCIPLE-DATA-MODELING --> # Data Modeling ## The one rule **Model for the queries you'll run, not the data you have.** Start by listing access patterns. Let them shape tables, indexes, and storage choice. A schema designed from the entity graph first almost always requires painful rework once query patterns solidify. ## Storage paradigm selection | Access pattern | Best fit | Avoid | |---|---|---| | Joins across entities, strong consistency, ad-hoc queries | Relational (Postgres, MySQL) | Document — you'll re-implement joins in app code | | Flexible schema, deep nested reads, document-centric writes | Document (MongoDB, Firestore) | Relational — schema rigidity fights you | | Point lookups, extreme throughput, mostly single-key reads | KV / wide-column (Redis, DynamoDB single-table) | Relational — joins at scale hurt. Note: DynamoDB supports composite keys and GSIs; it can serve multi-entity patterns when modeled carefully. | | Highly connected data, path/graph traversal queries | Graph (Neo4j, Amazon Neptune) | Relational — recursive CTEs degrade fast | **Inversion test:** if your primary latency-bound read requires unbounded recursive joins or joins that cannot be satisfied by available indexes, your storage paradigm may be wrong for the access pattern. ## Normalization vs denormalization Normalize when: - Write throughput is high and reads are secondary. - Data consistency across many rows matters more than read latency. - Access