Normalized 库 Catalog
你将构建什么
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
创建基础表
创建 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
创建 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
创建 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
查询完整 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
添加索引优化查询
为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。
代码-- 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 许多-到-许多关系.
相关资源
相关课程
- Normalization
学习 normal 表单.
- 关系
学习关于关系.
相关术语
- Normalization
Reducing redundancy.
- 外键
表链接.
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
- Normalization
Reducing redundancy.
- 外键
表链接.
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。