Normalized Library Catalog
What you'll build
A fully normalized (3NF) library catalog schema with separate tables for authors, publishers, categories, and books, connected by foreign keys and a many-to-many junction table.
Goals
- Design a normalized schema avoiding redundancy
- Separate authors, publishers, and categories into their own tables
- Use a junction table for the many-to-many book-author relationship
- Write queries that join multiple tables
Prerequisites
- Foreign keys and relationships
- Understanding of normalization concepts
- JOIN queries
Steps
- 1
Create base tables
Create publishers, authors, and categories tables. Each stores its data once — no duplication.
CodeCREATE TABLE publishers ( id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL ); CREATE TABLE authors ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE categories ( id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL );Expected result
Three normalized lookup tables are created.
- 2
Create books table
Create the books table with foreign keys to publisher and category. This avoids storing publisher/category names in every book row.
CodeCREATE TABLE books ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, publisher_id INTEGER REFERENCES publishers(id), category_id INTEGER REFERENCES categories(id), published_year INTEGER );Expected result
The books table references publishers and categories by foreign key.
- 3
Create book-authors junction
Books can have multiple authors and authors can write multiple books — a many-to-many relationship needs a junction table.
CodeCREATE TABLE book_authors ( book_id INTEGER REFERENCES books(id), author_id INTEGER REFERENCES authors(id), PRIMARY KEY (book_id, author_id) );Expected result
A junction table enables the many-to-many book-author relationship.
- 4
Query the full catalog
Insert sample data and write a query that joins all tables to show book titles with their authors, publisher, and category.
CodeINSERT INTO publishers (name) VALUES ('Tech Press'); INSERT INTO categories (name) VALUES ('Programming'); INSERT INTO authors (name) VALUES ('Ada Lovelace'); INSERT INTO books (title, publisher_id, category_id, published_year) VALUES ('Intro to SQL', 1, 1, 2023); INSERT INTO book_authors VALUES (1, 1); SELECT b.title, a.name AS author, p.name AS publisher, c.name AS category FROM books b INNER JOIN book_authors ba ON ba.book_id = b.id INNER JOIN authors a ON ba.author_id = a.id INNER JOIN publishers p ON b.publisher_id = p.id INNER JOIN categories c ON b.category_id = c.id;Expected result
A complete catalog entry showing the book with all its related data.
- 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 fully normalized schema, eliminating data redundancy by separating entities into their own tables and using a junction table for many-to-many relationships.
Related Resources
Related Lessons
- Normalization
Learn normal forms.
- Relationships
Learn about relationships.
Related Glossary
- Normalization
Reducing redundancy.
- Foreign Key
Table links.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Normalization
Reducing redundancy.
- Foreign Key
Table links.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.