Data Modeling — Cheat Sheet
Tags: data-modeling, databases, sql, nosql, normalization, er-diagram, schema-design
Level: intermediate
TL;DR: Concise reference for database modeling, comparing relational and non-relational patterns, normalization stages, and common interview design trade-offs.
Key concepts
Conceptual models map high-level business entities and relationships independently of implementation.
Logical models define entity attributes, unique keys, and relationship cardinalities without platform specifics.
Physical models map structures directly to DBMS-specific types, indexes, partitions, and storage constraints.
1NF requires atomic attribute values and a unique primary key identifier per row.
2NF eliminates partial dependencies by ensuring all non-key columns depend on the entire primary key.
3NF eliminates transitive dependencies; every non-key column must depend solely on the key.
Denormalization deliberately introduces redundancy to optimize read-intensive access patterns and avoid costly joins.
Relational schemas optimize for write integrity and flexible query joins with ACID compliance.
Document and NoSQL models prioritize access-path-driven structures, embedding data for single-query latency.
Fact and dimension tables form star or snowflake schemas optimized for analytical OLAP workloads.
Typical questions and ready answers
Q: How do you choose between embedding documents and referencing relationships in NoSQL?R: I look at access patterns and cardinalities: 1-to-few and atomic reads favor embedding, while unbounded growth and frequent updates favor referencing.
Q: When would you intentionally denormalize a relational database schema?R: I denormalize when read throughput on complex multi-table joins degrades performance and caching cannot safely satisfy latency requirements.
Q: What is the practical difference between OLTP and OLAP modeling?R: OLTP uses normalized 3NF structures to minimize write contention, whereas OLAP uses dimensional star schemas to speed up aggregate reporting.
Pitfalls and common mistakes
Over-normalizing high-throughput read workloads, causing catastrophic query latency from multi-way joins.
Unbounded array growth in document stores, causing single-document sizes to exceed engine limits.
Omitting foreign key constraints in transactional databases, causing orphan rows and corrupted referential integrity.
Applying relational modeling habits directly to document stores without designing around query access paths.
Selecting low-cardinality columns as shard keys, creating data skew and hot partition bottlenecks.
Snippets and commandsSQL
```-- Normalized 1:N relationship with referential integrity
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
total_amount NUMERIC(10, 2) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
// Embedded document model for single-read retrieval
{
"_id": "order_987",
"customer": {
"id": "cust_123",
"email": "user@example.com"
},
"items": [
{ "sku": "A101", "qty": 2, "unit_price": 19.99 },
{ "sku": "B202", "qty": 1, "unit_price": 45.00 }
],
"total_amount": 84.98,
"status": "placed"
}
```
To review
[ ] Review differences between BCNF and 3NF edge cases.
[ ] Practice modeling 1:N and N:M patterns in MongoDB schema designs.
[x] Master 1NF, 2NF, and 3NF normalization rules.
[x] Understand star schema design with fact and dimension tables.
References
Designing Data-Intensive Applications (Martin Kleppmann)
Database System Concepts (Silberschatz, Korth, Sudarshan)
