Skip to content

Normalized 库 Catalog

中等 约 25 分钟

你将构建什么

fully normalized (3NF) 库 catalog Schema 使用 separate 表 for authors, publishers, 分类, and books, 已连接通过 foreign 键 and 许多-到-许多 junction 表.

学习目标

  • 设计 normalized Schema avoiding redundancy
  • Separate authors, publishers, and 分类到 它们的 own 表
  • Use junction 表 for 许多-到-许多 book-author 关系
  • 编写查询那个连接多个表

前置要求

  • Foreign 键 and 关系
  • 理解的 normalization 概念
  • JOIN 查询

步骤

  1. 1

    创建基础表

    创建 publishers, authors, and 分类表. 每个存储它的数据一次 — 否 duplication.

    代码
    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
    );

    预期结果

    Three normalized lookup 表是 创建.

  2. 2

    创建 books 表

    创建 books 表使用 foreign 键到 publisher and 分类. 这个 avoids 存储 publisher/分类 names in 每个 book 行.

    代码
    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
    );

    预期结果

    books 表引用 publishers and 分类通过外键.

  3. 3

    创建 book-authors junction

    Books 能有 多个 authors and authors 能编写多个 books — 许多-到-许多关系需要 junction 表.

    代码
    CREATE TABLE book_authors (
      book_id    INTEGER REFERENCES books(id),
      author_id  INTEGER REFERENCES authors(id),
      PRIMARY KEY (book_id, author_id)
    );

    预期结果

    junction 表启用许多-到-许多 book-author 关系.

  4. 4

    查询完整 catalog

    Insert sample 数据 and 编写查询那个连接所有表 到 show book titles 使用它们的 authors, publisher, and 分类.

    代码
    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;

    预期结果

    完整 catalog 条目 showing book 使用所有它的 related 数据.

  5. 5

    添加索引优化查询

    为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。

    代码
    -- 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;

    预期结果

    EXPLAIN 输出显示使用 idx_orders_user_id 索引扫描而非全表扫描。

自己动手试试

你学到了什么

你设计 fully normalized Schema, eliminating 数据 redundancy 通过 separating entities 到它们的 own 表 and using junction 表 for 许多-到-许多关系.

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题