Database Design — Cheat Sheet

Tags: database, sql, modeling, normalization, indexing, relational, nosql

Level: intermediate

TL;DR: Best practices for data modeling, trade-offs between normalization and denormalization, index strategy, and relational integrity.

Key concepts

Conceptual, logical, and physical modeling are distinct phases of database design.

1NF enforces atomic values and unique records with a primary key.

2NF removes partial dependencies where non-key attributes depend on part of a composite key.

3NF eliminates transitive dependencies where non-key attributes depend on other non-key attributes.

Denormalization improves read throughput at the cost of write amplification and redundancy.

Primary keys guarantee entity uniqueness; foreign keys enforce referential integrity.

B-Tree indexes accelerate equality and range scans for high-cardinality columns.

Composite index column order must follow query filtering from most selective to least.

ACID compliance prioritizes consistency and durability over absolute write latency.

Partitioning and sharding address storage limits and query concurrency at scale.

Typical questions and ready answers

Q: When should you intentionally denormalize a relational schema?R: I denormalize when read latency is critical and repeated joins create query bottlenecks, provided the team can safely manage the resulting write amplification and cache invalidation.

Q: How do you decide between an integer surrogate key and a UUID?R: I pick auto-incrementing integers for compact B-Tree indexing and sequential disk writes, but switch to UUIDv7 when distributed generation or security against ID enumeration is required.

Q: What is the difference between clustered and non-clustered indexes?R: A clustered index determines the physical storage order of rows on disk, while a non-clustered index stores sorted pointers referencing the actual row locations.

Pitfalls and common mistakes

Over-indexing tables, which significantly degrades INSERT and UPDATE throughput.

Creating nullable foreign keys that lead to broken joins and orphan business records.

Storing unstructured JSON blobs for fields that require frequent filtering or aggregation.

Missing indexes on foreign key columns, causing full table scans during cascade operations.

Selecting low-cardinality columns (e.g., boolean flags) as leading columns in composite indexes.

Snippets and commandsSQL

```-- Composite index respecting the left-prefix rule

CREATE INDEX idx_orders_customer_status_created

ON orders (customer_id, status, created_at DESC);

-- Foreign key constraint with explicit referential action

ALTER TABLE order_items

ADD CONSTRAINT fk_order_items_order_id

FOREIGN KEY (order_id) REFERENCES orders (id)

ON DELETE CASCADE;

-- Identify missing indexes or full table scans in PostgreSQL

EXPLAIN ANALYZE

SELECT id, total_amount

FROM orders

WHERE customer_id = 42 AND status = 'COMPLETED';

```

To review

[ ] Isolation levels: Read Committed vs Repeatable Read vs Serializable

[ ] Covering indexes and index-only scan conditions

[x] Basic normalization forms (1NF, 2NF, 3NF)

[x] Primary key and foreign key constraint setups