Skip to content

Normalized Library Catalog

Medium ~25 min

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. 1

    Create base tables

    Create publishers, authors, and categories tables. Each stores its data once — no duplication.

    Code
    CREATE 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. 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.

    Code
    CREATE 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. 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.

    Code
    CREATE 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. 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.

    Code
    INSERT 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. 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

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