Database Design Basics

Introduction

Database design is the practice of modelling real-world entities (users, orders, products) into tables, columns and relationships. Good design is the difference between a fast, maintainable app and a swamp of bugs.

Beginner analogy: if writing SQL is cooking, designing the schema is laying out the kitchen — once it's wrong, everything is harder forever.

Understanding the topic

Core concepts to understand:

• Identify entities → become tables.
• Identify attributes → become columns with types.
• Identify relationships → primary/foreign keys.
• Apply normalization to remove redundancy.
• Add indexes for performance after access patterns are clear.

Syntax reference

Visual workflow / architecture:

Real World        Schema
─────────         ──────
User       ──▶  users(id, email, name)
Order      ──▶  orders(id, user_id, total, created_at)
Product    ──▶  products(id, name, price)
LineItem   ──▶  order_items(order_id, product_id, qty, price)

Real-world use

Every SaaS, marketplace and fintech is built on a few well-designed tables. Stripe's whole business runs on roughly a dozen core tables.

Best practices

• Start by drawing entities and relationships before writing CREATE TABLE.
• Prefer surrogate primary keys (id) unless natural keys are stable.
• Plan for change — apps evolve, schemas must too.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is an entity?
• Q2. Surrogate vs natural key — which to prefer?
• Q3. Why model relationships before writing SQL?
• Q4. What does normalization aim to remove?
• Q5. When do you add indexes — before or after access patterns are known?