Blog Database Schema
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
Create the authors table
Start by creating the
authorstable. Each author has an auto-incrementing id, a name, and an email. The email should be unique.CodeCREATE 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
Create the posts table
Create the
poststable. Each post belongs to an author (foreign key), has a title, body, and creation timestamp.CodeCREATE 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
Create the comments table
Create the
commentstable. Each comment belongs to a post (foreign key) and has an author name and text.CodeCREATE 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
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.
CodeINSERT 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
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
- Tables
Learn how to create tables.
- Relationships
Learn about foreign keys and relationships.
Related Glossary
- Foreign Key
How foreign keys link tables.
- Primary Key
Unique row identifiers.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Foreign Key
How foreign keys link tables.
- Primary Key
Unique row identifiers.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.