Database Fundamentals: SQL Basics & Relational Design — A Practical Guide

Why databases matter

From small web apps to enterprise systems, databases are the canonical place to store structured data. They provide persistence, concurrency control, queryability, and recovery. Choosing the right data model and understanding SQL will make your applications reliable and scalable.

Relational model & schema design

The relational model represents data as tables (relations) with rows and columns. Designing a good schema starts with identifying entities and relationships, choosing appropriate datatypes, and modeling one-to-many and many-to-many relationships.

Example domain: simple e-commerce

Entities: users, products, orders, order_items.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(200),
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(50) UNIQUE NOT NULL,
  title TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  qty INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
);

Foreign keys express referential integrity; use them to let the database enforce relationships instead of relying solely on application code.

SQL basics: SELECT, WHERE, ORDER BY

SQL is a declarative language: you describe the result you want, and the engine decides how to execute it. Core clauses:

-- Select columns
SELECT id, email, created_at
FROM users
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 50;

Keep queries explicit and readable: list columns (avoid SELECT * in production), use aliases, and prefer clear predicates in WHERE to allow indexes to be used.

JOINs explained with examples

JOINs combine rows from multiple tables. Understanding join types is critical to correct results and performance.

INNER JOIN

Returns rows where matching keys exist in both tables.

SELECT o.id AS order_id, u.email, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01';

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from left table, and matching rows from right; missing matches yield NULLs.

-- Orders with user info; include orders even if user was deleted (user columns NULL)
SELECT o.id, u.email
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

CROSS JOIN

Produces Cartesian product — rarely used except intentionally (e.g., generate combinations).

JOIN performance tips

• Ensure join columns are indexed (primary keys are indexed automatically).
• Avoid joining large tables without selective predicates.
• Be explicit with join conditions — accidental cross joins produce huge results.

Aggregates & GROUP BY

Aggregations summarize datasets: COUNT, SUM, AVG, MIN, MAX. Use GROUP BY to compute aggregates per group.

-- Sales per product
SELECT p.id, p.title, SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.title
ORDER BY revenue DESC
LIMIT 10;

Use HAVING to filter aggregated results (apply conditions after grouping).

Subqueries & Common Table Expressions (CTEs)

Subqueries let you embed queries. CTEs (WITH clause) improve readability and can be used for recursive queries.

-- CTE: recent orders and their totals
WITH recent_orders AS (
  SELECT id, user_id, total
  FROM orders
  WHERE created_at > now() - interval '30 days'
)
SELECT u.email, ro.total
FROM recent_orders ro
JOIN users u ON u.id = ro.user_id;

Use CTEs to break complex logic into named steps; note that in some engines non-materialized CTEs may be optimized away — benchmark if performance matters.

Transactions & isolation levels

Transactions group multiple statements into an atomic unit: they either fully succeed (commit) or fully fail (rollback). ACID properties (Atomicity, Consistency, Isolation, Durability) underpin transactional behavior.

BEGIN;

UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (...);

COMMIT;

Isolation levels

Common isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Higher isolation reduces anomalies but may reduce concurrency.

For many web apps, READ COMMITTED or REPEATABLE READ offers balance; choose after understanding your workload.

Indexes & performance

Indexes speed lookups by allowing the database to find rows without scanning the entire table. Use indexes thoughtfully — they speed reads but add write overhead and storage cost.

Types of indexes

• Primary key / unique indexes
• B-tree indexes (default common case)
• Hash indexes (specific engines)
• Partial and functional indexes (Postgres)

-- create index on foreign key used in joins
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- index on (product_id, created_at) for queries filtering by both
CREATE INDEX idx_item_prod_date ON order_items(product_id, order_id);

Indexing rules of thumb

• Index columns used in JOIN, WHERE, and ORDER BY.
• Prefer composite indexes matching query predicates left-to-right.
• Avoid indexing low-cardinality boolean columns alone.
• Monitor index usage (DB-specific tools) and drop unused indexes.

Normalization & denormalization

Normalization reduces duplication by organizing data into related tables (1NF, 2NF, 3NF, BCNF). It prevents anomalies and keeps data consistent. However, normalized schemas can require joins for common queries; sometimes denormalization improves read performance for high-throughput read workloads.

When to denormalize

• When read performance is critical and joins are expensive.
• When you need a precomputed aggregated value (e.g., cached counts).

If you denormalize, ensure you maintain consistency through application logic, triggers, or background jobs that update derived data.

Backups, security & maintenance

Backups

Regular backups are non-negotiable. Use periodic full backups plus incremental backups for large data sets. Test restores regularly — a backup you can't restore is useless.

Security

• Use least-privilege for DB users; avoid embedding superuser credentials in apps.
• Encrypt connections (TLS) and sensitive data at rest if required.
• Sanitize inputs and use parameterized queries / prepared statements to prevent SQL injection.

-- safe parameterized example (example pseudo-code)
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

Practical tuning tips

Performance tuning follows measurement: collect slow query logs, analyze query plans, and index appropriately. Common steps:

1. Enable slow query logging and identify hotspots.
2. EXPLAIN queries to see index usage and join order.
3. Add indexes for selective filters and join columns.
4. Consider query refactoring or caching (application-side cache, materialized views).
5. Scale reads with replicas; scale writes with sharding only when necessary.

EXPLAIN example (Postgres)

EXPLAIN ANALYZE
SELECT p.id, SUM(oi.qty) as sold
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id
ORDER BY sold DESC
LIMIT 10;

EXPLAIN ANALYZE returns actual runtime metrics; use it to validate the cost model and spot full-table scans or inefficient plans.

FAQ

Q: Should I use NoSQL or a relational database?

A: Choose the tool that fits the problem. Use relational databases for structured data with strong consistency requirements. Use NoSQL (document, key-value, wide-column) for flexible schemas, high write volumes, or specific access patterns — but weigh trade-offs on consistency, queryability, and operational complexity.

Q: When should I add an index?

A: Add an index when a column is frequently used in WHERE, JOIN, ORDER BY, or GROUP BY and the column is selective enough to benefit from index lookups. Monitor the impact on inserts/updates, and drop unused indexes.

Q: What is a materialized view?

A: A materialized view stores the result of a query physically for faster reads, with a mechanism to refresh it periodically or on demand. It is useful for expensive aggregations.

Key takeaways

• Start with clear schema design: identify entities, relationships, and constraints.
• Write readable SQL: explicit columns, clear predicates, and descriptive aliases.
• Use indexes to speed reads, but understand write costs and storage trade-offs.
• Transactions preserve consistency — pick an isolation level fitting your workload.
• Measure, use EXPLAIN, and iterate — real performance improvements come from evidence-driven changes.

Practice: design a schema for a small blog or e-commerce prototype, populate with sample data, write queries for common reports (sales by product, active users last 30 days), and use EXPLAIN to verify your indexes and query plans.