Database Design Basics (Beginner)

Theory

A database is a system that stores data and guarantees correctness, consistency, and safe concurrent access.

1) Start with entities and relationships

Most schemas become understandable by asking:

• What are the entities? (User, Order, Payment, Event)
• How do they relate? (one-to-many, many-to-many)
• What is the business rule behind the relationship?

2) Choose keys intentionally

• Primary key (PK): uniquely identifies a row
• Foreign key (FK): references another table’s PK
• Natural vs surrogate keys
  • Natural: derived from business meaning (email)
  • Surrogate: system-generated (user_id)

Beginner rule: use surrogate integer/UUID keys unless you have a strong reason not to.

3) Normalize for clarity (but not dogma)

Normalization reduces redundancy and update anomalies. Practical approach:

• normalize early enough to avoid contradictions
• denormalize later if you can justify the tradeoff

4) Index to support your queries

Indexes accelerate reads, but slow writes. A good heuristic:

• index the columns used in WHERE filters
• index join columns used for matching rows
• index sort keys if you frequently run ORDER BY

Code Example (SQL: schema + relationships)

-- Users table
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL,
  order_total_cents BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Example query: get orders for a user
SELECT
  o.order_id,
  o.status,
  o.order_total_cents
FROM orders o
WHERE o.user_id = 'c0a80123-0000-0000-0000-000000000001'
ORDER BY o.created_at DESC;

Practice

1. Model a simple “blog” domain:
   • Entities: users, posts, comments
   • Relationships: user→posts, post→comments
2. Write the DDL for the tables and add PK/FK constraints.
3. Add at least one index based on a query you expect to run often.

Common pitfalls

• Using text fields as keys without considering update/collision scenarios
• Adding indexes “just in case” (hurts writes)
• Designing tables before understanding query patterns