多附件关联文章的设计方案

问题描述

需要设计一个系统，允许一篇文章关联多个附件（如图片、文档、视频等）。

数据库设计方案

方案一：外键关联表（推荐）

-- 文章表
CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 附件表
CREATE TABLE attachments (
    attachment_id INT PRIMARY KEY AUTO_INCREMENT,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(512) NOT NULL,
    file_type VARCHAR(50),
    file_size BIGINT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章-附件关联表
CREATE TABLE article_attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT NOT NULL,
    attachment_id INT NOT NULL,
    sort_order INT DEFAULT 0,  -- 附件排序
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
    FOREIGN KEY (attachment_id) REFERENCES attachments(attachment_id) ON DELETE CASCADE,
    UNIQUE KEY (article_id, attachment_id)  -- 防止重复关联
);

方案二：JSON字段存储（适用于简单场景）

CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    attachments JSON,  -- 存储附件ID数组，如[1, 3, 5]
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

业务逻辑实现

上传并关联附件流程

1. 用户上传文件到服务器
2. 系统将文件信息存入attachments表
3. 在article_attachments表中创建文章与附件的关联记录

获取文章及其附件

-- 获取文章及其所有附件
SELECT a.*, 
       GROUP_CONCAT(at.file_name) AS attachment_names,
       GROUP_CONCAT(at.file_path) AS attachment_paths
FROM articles a
LEFT JOIN article_attachments aa ON a.article_id = aa.article_id
LEFT JOIN attachments at ON aa.attachment_id = at.attachment_id
WHERE a.article_id = ?
GROUP BY a.article_id;

文件存储建议

1. 使用独立文件服务器或云存储服务（如AWS S3、阿里云OSS）
2. 按日期分目录存储，如uploads/2023/11/15/filename.ext
3. 存储文件时生成唯一文件名，防止冲突

扩展考虑

1. 附件类型限制：可在应用层或数据库层限制允许的附件类型
2. 附件大小限制：在应用层设置最大上传限制
3. 附件预览：对于图片、PDF等可预览文件，可生成缩略图
4. 附件下载权限：可扩展关联表增加权限控制字段

性能优化

1. 为关联表添加索引：CREATE INDEX idx_article ON article_attachments(article_id)
2. 对于大量附件的文章，考虑分页查询附件
3. 使用缓存（如Redis）缓存热门文章的附件列表

这个设计方案提供了灵活的多附件关联机制，同时保持了数据库的规范化和查询效率。