寻求多宗教典籍存储SQL表结构优化建议（附现有建表代码）

我正在设计用于存储多部宗教典籍/经文的SQL表结构，已完成基础版本，但由于对SQL最佳实践了解有限，希望专业人士指导该结构是否可优化。以下是我设计的建表代码：

CREATE TABLE tbl_text (
    col_text_id int,
    primary key (col_text_id)
);

CREATE TABLE tbl_language (
    col_language_id int,
    col_display_name varchar(32) unique not null,
    col_short_name varchar(3) unique not null,
    primary key (col_language_id)
);

CREATE TABLE tbl_translation (
    col_translation_id int,
    col_language_id int not null,
    col_text_id int not null,
    col_display_value text not null,
    primary key (col_translation_id),
    foreign key (col_language_id) references tbl_language (col_language_id),
    foreign key (col_text_id) references tbl_text (col_text_id)
);

CREATE TABLE tbl_book (
    col_book_id int,
    col_title int not null,
    col_author int not null,
    primary key (col_book_id),
    foreign key (col_title) references tbl_text (col_text_id),
    foreign key (col_author) references tbl_text (col_text_id)
);

CREATE TABLE tbl_chapter (
    col_chapter_id int,
    col_book_id int not null,
    col_name int not null,
    col_sequence int not null,
    primary key (col_chapter_id),
    foreign key (col_book_id) references tbl_book (col_book_id),
    foreign key (col_name) references tbl_text (col_text_id) 
);

CREATE TABLE tbl_paragraph (
    col_paragraph_id int,
    col_book_id int not null,
    col_chapter_id int,
    col_text int not null,
    primary key (col_paragraph_id),
    foreign key (col_book_id) references tbl_book (col_book_id),
    foreign key (col_chapter_id) references tbl_chapter (col_chapter_id),
    foreign key (col_text) references tbl_text(col_text_id)
);

优化方向与建议 #

1. 主键自动化与字段命名简化

• 所有主键字段建议添加自增属性（MySQL用AUTO_INCREMENT，PostgreSQL用SERIAL），避免手动维护主键值，降低重复或冲突风险。
• 去掉tbl_、col_前缀，改用更简洁的命名（如texts替代tbl_text，text_id替代col_text_id），既保持语义清晰，又符合SQL命名的简洁性原则。

2. 完善text表的核心信息

原tbl_text仅存主键，无法明确文本的用途或内容。建议添加：

• text_key：唯一标识文本的用途（如"bible_genesis_title"），方便关联查询时识别；
• original_content：存储原文内容（如果需要保留原始语言的文本）。

3. 拆分作者关联逻辑

原tbl_book的col_author关联到tbl_text，但作者信息通常是独立实体，建议单独创建authors表存储作者名称，再与books表关联。如果需要支持多语言作者名，再通过translations表关联，这样逻辑更清晰。

4. 消除冗余关联

tbl_paragraph同时关联col_book_id和col_chapter_id，但col_chapter_id已经关联到col_book_id，属于冗余关联。可以去掉col_book_id，通过col_chapter_id间接关联到books表，减少数据冗余和更新时的不一致风险。

5. 添加必要的约束与索引

• 在translations表中，添加(language_id, text_id)联合唯一约束，避免同一文本同一语言存在重复翻译；
• 在chapters表中，添加(book_id, sequence)联合唯一约束，确保同一书籍的章节顺序不重复；
• 为外键字段（如chapters.book_id、paragraphs.chapter_id）添加索引，提升大数量级下的查询性能。

6. 补充段落顺序字段

原tbl_paragraph缺少段落顺序标识，建议添加sequence字段，确保章节内的段落可以按正确顺序展示，同时添加联合唯一约束避免同一章节内段落顺序重复。

优化后的示例代码 #

-- 存储可翻译文本的核心表
CREATE TABLE texts (
    text_id int AUTO_INCREMENT PRIMARY KEY,
    text_key varchar(64) UNIQUE NOT NULL, -- 唯一标识文本用途
    original_content text -- 可选：存储原始语言文本
);

-- 语言表
CREATE TABLE languages (
    language_id int AUTO_INCREMENT PRIMARY KEY,
    display_name varchar(32) UNIQUE NOT NULL,
    short_name varchar(3) UNIQUE NOT NULL
);

-- 翻译表：关联文本与语言
CREATE TABLE translations (
    translation_id int AUTO_INCREMENT PRIMARY KEY,
    language_id int NOT NULL,
    text_id int NOT NULL,
    display_value text NOT NULL,
    FOREIGN KEY (language_id) REFERENCES languages(language_id),
    FOREIGN KEY (text_id) REFERENCES texts(text_id),
    UNIQUE (language_id, text_id) -- 避免重复翻译
);

-- 作者表：独立存储作者信息
CREATE TABLE authors (
    author_id int AUTO_INCREMENT PRIMARY KEY,
    author_name varchar(128) NOT NULL
);

-- 书籍表
CREATE TABLE books (
    book_id int AUTO_INCREMENT PRIMARY KEY,
    title_text_id int NOT NULL, -- 关联texts表中的书籍标题
    author_id int NOT NULL, -- 关联authors表
    FOREIGN KEY (title_text_id) REFERENCES texts(text_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- 章节表
CREATE TABLE chapters (
    chapter_id int AUTO_INCREMENT PRIMARY KEY,
    book_id int NOT NULL,
    name_text_id int NOT NULL, -- 关联texts表中的章节名称
    sequence int NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (name_text_id) REFERENCES texts(text_id),
    UNIQUE (book_id, sequence) -- 同一书籍章节顺序唯一
);

-- 段落表
CREATE TABLE paragraphs (
    paragraph_id int AUTO_INCREMENT PRIMARY KEY,
    chapter_id int NULL, -- 允许无章节的段落（如部分典籍结构）
    text_id int NOT NULL, -- 关联texts表中的段落内容
    sequence int NOT NULL,
    FOREIGN KEY (chapter_id) REFERENCES chapters(chapter_id),
    FOREIGN KEY (text_id) REFERENCES texts(text_id),
    UNIQUE (chapter_id, sequence) -- 同一章节段落顺序唯一
);

内容的提问来源于stack exchange，提问作者Pratyush Choudhary