Skip to content

Blog Database Schema

Beginner ~15 min

What you'll build

A SQL script that creates a complete blog database with three related tables: authors, posts, and comments, connected by foreign keys.

Goals

  • Design a schema with three related tables
  • Use primary keys and foreign keys to create relationships
  • Apply NOT NULL, UNIQUE, and DEFAULT constraints
  • Insert sample data into all three tables

Prerequisites

  • Basic SQL syntax
  • Understanding of tables and rows
  • Primary keys and foreign keys

Steps

  1. 1

    Create the authors table

    Start by creating the authors table. Each author has an auto-incrementing id, a name, and an email. The email should be unique.

    Code
    CREATE TABLE authors (
      id     SERIAL PRIMARY KEY,
      name   TEXT NOT NULL,
      email  TEXT UNIQUE NOT NULL
    );

    Expected result

    The authors table is created with three columns and appropriate constraints.

  2. 2

    Create the posts table

    Create the posts table. Each post belongs to an author (foreign key), has a title, body, and creation timestamp.

    Code
    CREATE TABLE posts (
      id          SERIAL PRIMARY KEY,
      author_id   INTEGER REFERENCES authors(id),
      title       TEXT NOT NULL,
      body        TEXT NOT NULL,
      created_at  TIMESTAMP DEFAULT NOW()
    );

    Expected result

    The posts table is created with a foreign key linking each post to an author.

  3. 3

    Create the comments table

    Create the comments table. Each comment belongs to a post (foreign key) and has an author name and text.

    Code
    CREATE TABLE comments (
      id         SERIAL PRIMARY KEY,
      post_id    INTEGER REFERENCES posts(id),
      author     TEXT NOT NULL,
      body       TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    );

    Expected result

    The comments table is created, linked to posts by a foreign key.

  4. 4

    Insert sample data

    Insert one author, one post by that author, and one comment on that post. This tests that your foreign keys work correctly.

    Code
    INSERT INTO authors (name, email) VALUES ('Ada Lovelace', '[email protected]');
    
    INSERT INTO posts (author_id, title, body) VALUES (1, 'My First Post', 'Hello world!');
    
    INSERT INTO comments (post_id, author, body) VALUES (1, 'Grace', 'Great post!');

    Expected result

    Three rows are inserted successfully, confirming the relationships work.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You designed a relational schema with three tables connected by foreign keys, applied constraints, and inserted related data — the foundation of every relational database.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic