kerryprops_bookmarks/项目/book/系统表结构.md

数据库：mysql

设计一套表结构，需要维护作者，封面，书籍类型，书籍基础信息，书籍基础信息历史编辑记录，章节标题，章节内容，章节内容历史修改记录，

MySQL 数据库表结构设计：书籍管理系统

核心表结构

1. 作者表 (authors)

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    bio TEXT,
    birth_date DATE,
    death_date DATE,
    nationality VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 书籍类型表 (book_types)

CREATE TABLE book_types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 书籍基础信息表 (books)

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    type_id INT NOT NULL,
    cover_image_url VARCHAR(255),
    description TEXT,
    isbn VARCHAR(20),
    publish_date DATE,
    publisher VARCHAR(100),
    page_count INT,
    language VARCHAR(30),
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (type_id) REFERENCES book_types(type_id)
);

4. 书籍信息历史记录表 (book_history)

CREATE TABLE book_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    editor_id INT, -- 假设有用户系统，记录编辑者ID
    title VARCHAR(200),
    author_id INT,
    type_id INT,
    cover_image_url VARCHAR(255),
    description TEXT,
    isbn VARCHAR(20),
    publish_date DATE,
    publisher VARCHAR(100),
    page_count INT,
    language VARCHAR(30),
    status ENUM('draft', 'published', 'archived'),
    change_reason TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (type_id) REFERENCES book_types(type_id)
);

5. 章节表 (chapters)

CREATE TABLE chapters (
    chapter_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    chapter_number INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    word_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    UNIQUE KEY (book_id, chapter_number)
);

6. 章节内容表 (chapter_contents)

CREATE TABLE chapter_contents (
    content_id INT AUTO_INCREMENT PRIMARY KEY,
    chapter_id INT NOT NULL,
    content LONGTEXT NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (chapter_id) REFERENCES chapters(chapter_id)
);

7. 章节内容历史记录表 (chapter_content_history)

CREATE TABLE chapter_content_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    content_id INT NOT NULL,
    chapter_id INT NOT NULL,
    editor_id INT, -- 假设有用户系统，记录编辑者ID
    content LONGTEXT NOT NULL,
    version INT NOT NULL,
    change_reason TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (content_id) REFERENCES chapter_contents(content_id),
    FOREIGN KEY (chapter_id) REFERENCES chapters(chapter_id)
);

索引设计

-- 书籍表索引
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_type ON books(type_id);

-- 章节表索引
CREATE INDEX idx_chapters_book ON chapters(book_id);
CREATE INDEX idx_chapters_number ON chapters(book_id, chapter_number);

-- 章节内容索引
CREATE INDEX idx_chapter_content ON chapter_contents(chapter_id);

-- 历史记录索引
CREATE INDEX idx_book_history ON book_history(book_id);
CREATE INDEX idx_chapter_history ON chapter_content_history(chapter_id);

视图设计（可选）

书籍完整信息视图

CREATE VIEW book_full_info AS
SELECT 
    b.book_id, b.title, a.name AS author_name, bt.type_name AS book_type,
    b.cover_image_url, b.description, b.isbn, b.publish_date, b.publisher,
    b.page_count, b.language, b.status, b.created_at, b.updated_at,
    (SELECT COUNT(*) FROM chapters c WHERE c.book_id = b.book_id) AS chapter_count
FROM 
    books b
JOIN 
    authors a ON b.author_id = a.author_id
JOIN 
    book_types bt ON b.type_id = bt.type_id;

章节完整信息视图

CREATE VIEW chapter_full_info AS
SELECT 
    c.chapter_id, c.book_id, b.title AS book_title, c.chapter_number,
    c.title AS chapter_title, cc.content, cc.version AS content_version,
    c.word_count, c.created_at, c.updated_at
FROM 
    chapters c
JOIN 
    books b ON c.book_id = b.book_id
JOIN 
    chapter_contents cc ON c.chapter_id = cc.chapter_id
WHERE 
    cc.version = (SELECT MAX(version) FROM chapter_contents WHERE chapter_id = c.chapter_id);

触发器设计（可选）

自动更新章节字数统计

DELIMITER //
CREATE TRIGGER update_word_count
AFTER INSERT ON chapter_contents
FOR EACH ROW
BEGIN
    UPDATE chapters 
    SET word_count = CHAR_LENGTH(NEW.content) - CHAR_LENGTH(REPLACE(NEW.content, ' ', '')) + 1
    WHERE chapter_id = NEW.chapter_id;
END//
DELIMITER ;

书籍信息变更历史记录

DELIMITER //
CREATE TRIGGER book_change_history
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
    INSERT INTO book_history (
        book_id, editor_id, title, author_id, type_id, cover_image_url,
        description, isbn, publish_date, publisher, page_count, language, status
    ) VALUES (
        OLD.book_id, NULL, OLD.title, OLD.author_id, OLD.type_id, OLD.cover_image_url,
        OLD.description, OLD.isbn, OLD.publish_date, OLD.publisher, OLD.page_count,
        OLD.language, OLD.status
    );
END//
DELIMITER ;

这套表结构设计涵盖了您要求的所有元素，并提供了良好的扩展性和数据完整性保障。根据实际需求，您可以进一步调整字段类型、添加约束或扩展功能。