PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

Database Design & Constraints

Verify normal forms, map key constraints (FK, PK, UNIQUE), and enforce referential check rules.

Relational Theory & Optimization Architecture

Database design and administration principles govern how data structures are physically organized and maintained for relational integrity, concurrency, and durability. Covering entity-relationship modeling, Boyce-Codd Normal Form (BCNF), index architecture (B-Tree vs. Hash vs. GiST), and ACID transaction guarantees, this domain bridges software architecture and operational database administration.

Physical Storage & Buffer Pool Mechanics

At the physical storage level, relational engines maintain Write-Ahead Logging (WAL) to enforce ACID atomicity and durability. Before any page mutation is made in memory, the change is serialized sequentially to the WAL disk log. B-Tree indexes store sorted key-pointer pairs across balanced hierarchical nodes, providing O(log N) lookup and range scan capabilities.

Transaction Isolation Levels and Index Mechanics

SQL transactions adhere to isolation levels defined by ANSI SQL: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level mitigates specific concurrency anomalies: Dirty Reads, Non-Repeatable Reads, and Phantom Reads. Database engines use Multi-Version Concurrency Control (MVCC) or two-phase locking (2PL) to balance concurrency with isolation.

Worked Query Walkthrough: Designing an Idempotent E-Commerce Inventory Schema

CREATE TABLE inventory_items (
    sku_id VARCHAR(64) PRIMARY KEY,
    warehouse_id INTEGER NOT NULL,
    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0),
    version_lock INTEGER NOT NULL DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_warehouse_sku UNIQUE (warehouse_id, sku_id)
);
CREATE INDEX idx_inventory_lookup ON inventory_items (warehouse_id, stock_quantity);

Step-by-Step Row Evaluation Matrix

OperationLock AcquiredConstraint CheckWAL SerializedIsolation Safety Guarantee
BEGIN TRANSACTIONTransaction IDNoneActive Tx LogSnapshot baseline created
UPDATE inventory_items SET stock_quantity = stock_quantity - 1...Row ExclusiveCHECK (qty >= 0)Update LogPrevent negative inventory
COMMITRelease LockFinalizedFlushed to WALAtomic state change durable

Runtime Trace Analysis

The CHECK constraint prevents overselling at the database level regardless of application bugs. The composite index on (warehouse_id, stock_quantity) enables covering index seeks for fast stock-availability checks without scanning base table pages.

Common Traps, Anti-Patterns & Debugging Solutions

1. Over-Indexing Low-Cardinality Columns

Problematic Syntax (Anti-Pattern)CREATE INDEX idx_is_active ON users(is_active);
Optimized AlternativeCREATE INDEX idx_active_users ON users(created_at) WHERE is_active = 1;

Indexing a boolean column with only two distinct values (50/50 distribution) is rarely used by query planners (which prefer sequential scans) while slowing down every INSERT, UPDATE, and DELETE. Use a partial index instead.

2. Storing Delimited Lists in a Single Column (1NF Violation)

Problematic Syntax (Anti-Pattern)CREATE TABLE users (id INT, tag_list TEXT); -- 'python,sql,docker'
Optimized AlternativeCREATE TABLE user_tags (user_id INT, tag VARCHAR(50), PRIMARY KEY(user_id, tag));

Storing comma-separated values violates First Normal Form, prevents index usage, complicates join operations, and makes relational integrity constraints impossible to enforce.

3. Missing Foreign Key Indexes

Problematic Syntax (Anti-Pattern)CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(id)); -- Missing index on customer_id
Optimized AlternativeCREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(id)); CREATE INDEX idx_orders_customer ON orders(customer_id);

While Primary Keys are automatically indexed, Foreign Key columns are not. Omitting an index on foreign keys degrades parent-child JOIN performance and triggers full table locks during parent record deletions.

Production Strategy & Query Engineering Best Practices

Database design requires careful consideration of indexing trade-offs and transaction isolation. Always evaluate how B-Tree indexes accelerate reads at the cost of write amplification, and understand optimistic vs. pessimistic concurrency control.

Interactive WebAssembly SQL Playground

Client-Side SQLite Engine
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
EmployeeEmpID, Name, DeptID, Salary, ManagerID8 rowsStaff directory with salaries and reporting structure
DepartmentDeptID, DeptName, Budget8 rowsOrganizational departments and annual budgets
ProjectProjectID, ProjectName, DeptID, Deadline5 rowsActive enterprise engineering projects
Interactive Query Workspace

Run custom SQL queries against the Employee, Department, Project schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.

Frequently Asked Questions on Database Design & Constraints

What are the four ACID properties in database management systems?

Atomicity (all operations succeed or all rollback), Consistency (transactions preserve schema constraints), Isolation (concurrent transactions execute without interference), and Durability (committed changes survive crashes).

Why do B-Tree indexes provide faster lookups than unindexed tables?

B-Tree indexes maintain sorted keys across a balanced multi-way tree, allowing the database to locate any record in O(log N) disk page seeks rather than an O(N) full table scan.

What is the difference between a clustered and non-clustered index?

A clustered index defines the physical order of table rows on disk (there can only be one per table). A non-clustered index maintains a separate sorted structure containing pointers back to the base rows.

What is a database deadlock and how is it resolved?

A deadlock occurs when two concurrent transactions hold locks on resources the other needs, creating a circular wait. The database engine's deadlock detector identifies the cycle and aborts one transaction to break the freeze.

Explore Other SQL Practice Tracks