博客系统数据库设计

PieroPc

1423人浏览 · 2024-12-30 14:40:26

PieroPc · 2024-12-30 14:40:26 发布

一、博客系统数据库设计

用户管理

users（用户表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	用户ID
username	VARCHAR(50)	NOT NULL, UNIQUE	用户名
email	VARCHAR(100)	NOT NULL, UNIQUE	邮箱
password_hash	VARCHAR(255)	NOT NULL	密码哈希
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE	更新时间

内容管理

posts（文章表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	文章ID
user_id	INT	NOT NULL, FOREIGN KEY	作者ID
title	VARCHAR(200)	NOT NULL	标题
content	TEXT	NOT NULL	内容
status	ENUM	DEFAULT 'draft'	状态(draft/published)
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE	更新时间

categories（分类表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	分类ID
name	VARCHAR(50)	NOT NULL, UNIQUE	分类名称
description	VARCHAR(200)	-	分类描述

comments（评论表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	评论ID
post_id	INT	NOT NULL, FOREIGN KEY	文章ID
user_id	INT	NOT NULL, FOREIGN KEY	用户ID
content	TEXT	NOT NULL	评论内容
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	创建时间

权限管理

roles（角色表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	角色ID
name	VARCHAR(50)	NOT NULL, UNIQUE	角色名称
description	VARCHAR(200)	-	角色描述

permissions（权限表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	权限ID
name	VARCHAR(50)	NOT NULL, UNIQUE	权限名称
description	VARCHAR(200)	-	权限描述

统计和互动

post_views（文章访问统计表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	访问ID
post_id	INT	NOT NULL, FOREIGN KEY	文章ID
user_id	INT	FOREIGN KEY	访问用户ID
ip_address	VARCHAR(45)	NOT NULL	访问IP
viewed_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	访问时间

post_likes（文章点赞表）

字段名	类型	约束	说明
post_id	INT	PRIMARY KEY, FOREIGN KEY	文章ID
user_id	INT	PRIMARY KEY, FOREIGN KEY	用户ID
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	点赞时间

文件管理

attachments（附件表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	附件ID
post_id	INT	NOT NULL, FOREIGN KEY	文章ID
file_name	VARCHAR(255)	NOT NULL	文件名
file_path	VARCHAR(255)	NOT NULL	文件路径
file_type	VARCHAR(50)	NOT NULL	文件类型
file_size	INT	NOT NULL	文件大小
uploaded_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	上传时间

订阅系统

subscriptions（订阅表）

字段名	类型	约束	说明
id	INT	PRIMARY KEY, AUTO_INCREMENT	订阅ID
subscriber_id	INT	NOT NULL, FOREIGN KEY	订阅者ID
target_id	INT	NOT NULL, FOREIGN KEY	被订阅者ID
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	订阅时间

关联表

post_categories（文章分类关联表）

字段名	类型	约束	说明
post_id	INT	PRIMARY KEY, FOREIGN KEY	文章ID
category_id	INT	PRIMARY KEY, FOREIGN KEY	分类ID

post_tags（文章标签关联表）

字段名	类型	约束	说明
post_id	INT	PRIMARY KEY, FOREIGN KEY	文章ID
tag_id	INT	PRIMARY KEY, FOREIGN KEY	标签ID

user_roles（用户角色关联表）

字段名	类型	约束	说明
user_id	INT	PRIMARY KEY, FOREIGN KEY	用户ID
role_id	INT	PRIMARY KEY, FOREIGN KEY	角色ID

role_permissions（角色权限关联表）

字段名	类型	约束	说明
role_id	INT	PRIMARY KEY, FOREIGN KEY	角色ID
permission_id	INT	PRIMARY KEY, FOREIGN KEY	权限ID

索引说明

post_views_post_id_index：优化文章访问量查询
post_views_user_id_index：优化用户访问历史查询
post_likes_user_id_index：优化用户点赞历史查询
subscriptions_target_id_index：优化订阅者查询

二、SQL 代码：

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200)
);

-- 文章分类关联表
CREATE TABLE post_categories (
    post_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 文章标签关联表
CREATE TABLE post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

-- 角色表
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200)
);

-- 用户角色关联表
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- 权限表
CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200)
);

-- 角色权限关联表
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

-- 文章访问统计表
CREATE TABLE post_views (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT,  -- 可以为空，表示未登录用户
    ip_address VARCHAR(45) NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 文章点赞表
CREATE TABLE post_likes (
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 图片附件表
CREATE TABLE attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NOT NULL,
    file_size INT NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- 订阅表
CREATE TABLE subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscriber_id INT NOT NULL,  -- 订阅者
    target_id INT NOT NULL,      -- 被订阅的用户
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES users(id),
    FOREIGN KEY (target_id) REFERENCES users(id)
);

-- 订阅通知表
CREATE TABLE subscription_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subscription_id INT NOT NULL,
    post_id INT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- 添加索引
ALTER TABLE post_views ADD INDEX idx_post_views_post_id (post_id);
ALTER TABLE post_views ADD INDEX idx_post_views_user_id (user_id);
ALTER TABLE post_likes ADD INDEX idx_post_likes_user_id (user_id);
ALTER TABLE subscriptions ADD INDEX idx_subscriptions_target_id (target_id);

三、Django Models.py

from django.db import models
from django.contrib.auth.models import AbstractUser
from django.utils import timezone

class User(AbstractUser):
    """用户模型
    继承自Django的AbstractUser，扩展了邮箱验证和时间戳功能
    """
    email = models.EmailField(unique=True, verbose_name='邮箱')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')

    class Meta:
        db_table = 'users'
        verbose_name = '用户'
        verbose_name_plural = '用户列表'

class Post(models.Model):
    """文章模型
    博客文章的核心模型，包含文章的基本信息和状态
    """
    STATUS_CHOICES = (
        ('draft', '草稿'),
        ('published', '已发布'),
    )
    
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts', verbose_name='作者')
    title = models.CharField(max_length=200, verbose_name='标题')
    content = models.TextField(verbose_name='内容')
    status = models.CharField(max_length=10, choices=STATUS_CHOICES, default='draft', verbose_name='状态')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')
    categories = models.ManyToManyField('Category', through='PostCategory', verbose_name='分类')
    tags = models.ManyToManyField('Tag', through='PostTag', verbose_name='标签')

    class Meta:
        db_table = 'posts'
        verbose_name = '文章'
        verbose_name_plural = '文章列表'
        ordering = ['-created_at']  # 按创建时间倒序排列

class Category(models.Model):
    """分类模型
    文章分类，支持多级分类结构
    """
    name = models.CharField(max_length=50, unique=True, verbose_name='分类名称')
    description = models.CharField(max_length=200, blank=True, verbose_name='分类描述')

    class Meta:
        db_table = 'categories'
        verbose_name = '分类'
        verbose_name_plural = '分类列表'

class Tag(models.Model):
    """标签模型
    文章标签，用于文章的快速分类和检索
    """
    name = models.CharField(max_length=50, unique=True, verbose_name='标签名称')

    class Meta:
        db_table = 'tags'
        verbose_name = '标签'
        verbose_name_plural = '标签列表'

class PostCategory(models.Model):
    """文章分类关联模型
    实现文章和分类的多对多关系
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, verbose_name='文章')
    category = models.ForeignKey(Category, on_delete=models.CASCADE, verbose_name='分类')

    class Meta:
        db_table = 'post_categories'
        unique_together = ('post', 'category')
        verbose_name = '文章分类'
        verbose_name_plural = '文章分类关联'

class PostTag(models.Model):
    """文章标签关联模型
    实现文章和标签的多对多关系
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, verbose_name='文章')
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE, verbose_name='标签')

    class Meta:
        db_table = 'post_tags'
        unique_together = ('post', 'tag')
        verbose_name = '文章标签'
        verbose_name_plural = '文章标签关联'

class Comment(models.Model):
    """评论模型
    文章评论功能，支持用户评论
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments', verbose_name='文章')
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='comments', verbose_name='评论者')
    content = models.TextField(verbose_name='评论内容')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='评论时间')

    class Meta:
        db_table = 'comments'
        verbose_name = '评论'
        verbose_name_plural = '评论列表'
        ordering = ['-created_at']

class Role(models.Model):
    """角色模型
    用户角色定义，用于权限管理
    """
    name = models.CharField(max_length=50, unique=True, verbose_name='角色名称')
    description = models.CharField(max_length=200, blank=True, verbose_name='角色描述')
    permissions = models.ManyToManyField('Permission', through='RolePermission', verbose_name='权限')

    class Meta:
        db_table = 'roles'
        verbose_name = '角色'
        verbose_name_plural = '角色列表'

class Permission(models.Model):
    """权限模型
    系统权限定义
    """
    name = models.CharField(max_length=50, unique=True, verbose_name='权限名称')
    description = models.CharField(max_length=200, blank=True, verbose_name='权限描述')

    class Meta:
        db_table = 'permissions'
        verbose_name = '权限'
        verbose_name_plural = '权限列表'

class UserRole(models.Model):
    """用户角色关联模型
    实现用户和角色的多对多关系
    """
    user = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='用户')
    role = models.ForeignKey(Role, on_delete=models.CASCADE, verbose_name='角色')

    class Meta:
        db_table = 'user_roles'
        unique_together = ('user', 'role')
        verbose_name = '用户角色'
        verbose_name_plural = '用户角色关联'

class RolePermission(models.Model):
    """角色权限关联模型
    实现角色和权限的多对多关系
    """
    role = models.ForeignKey(Role, on_delete=models.CASCADE, verbose_name='角色')
    permission = models.ForeignKey(Permission, on_delete=models.CASCADE, verbose_name='权限')

    class Meta:
        db_table = 'role_permissions'
        unique_together = ('role', 'permission')
        verbose_name = '角色权限'
        verbose_name_plural = '角色权限关联'

class PostView(models.Model):
    """文章访问统计模型
    记录文章的访问历史，支持匿名访问统计
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='views', verbose_name='文章')
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='post_views', verbose_name='访问者')
    ip_address = models.GenericIPAddressField(verbose_name='访问IP')
    viewed_at = models.DateTimeField(auto_now_add=True, verbose_name='访问时间')

    class Meta:
        db_table = 'post_views'
        verbose_name = '文章访问'
        verbose_name_plural = '文章访问记录'
        indexes = [
            models.Index(fields=['post']),
            models.Index(fields=['user']),
        ]

class PostLike(models.Model):
    """文章点赞模型
    记录用户对文章的点赞信息
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='likes', verbose_name='文章')
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='post_likes', verbose_name='点赞者')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='点赞时间')

    class Meta:
        db_table = 'post_likes'
        unique_together = ('post', 'user')
        verbose_name = '文章点赞'
        verbose_name_plural = '文章点赞记录'
        indexes = [
            models.Index(fields=['user']),
        ]

class Attachment(models.Model):
    """附件模型
    管理文章相关的文件附件
    """
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='attachments', verbose_name='文章')
    file_name = models.CharField(max_length=255, verbose_name='文件名')
    file_path = models.CharField(max_length=255, verbose_name='文件路径')
    file_type = models.CharField(max_length=50, verbose_name='文件类型')
    file_size = models.IntegerField(verbose_name='文件大小')
    uploaded_at = models.DateTimeField(auto_now_add=True, verbose_name='上传时间')

    class Meta:
        db_table = 'attachments'
        verbose_name = '附件'
        verbose_name_plural = '附件列表'

class Subscription(models.Model):
    """订阅模型
    实现用户间的订阅关系
    """
    subscriber = models.ForeignKey(User, on_delete=models.CASCADE, related_name='subscriptions', verbose_name='订阅者')
    target = models.ForeignKey(User, on_delete=models.CASCADE, related_name='subscribers', verbose_name='被订阅者')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='订阅时间')

    class Meta:
        db_table = 'subscriptions'
        unique_together = ('subscriber', 'target')
        verbose_name = '订阅关系'
        verbose_name_plural = '订阅关系列表'
        indexes = [
            models.Index(fields=['target']),
        ]

这个Django models实现包含以下特点：

使用AbstractUser扩展Django的用户模型
使用related_name定义反向关系
使用through模型定义多对多关系
添加了适当的索引优化查询性能
使用Meta类定义表名和其他元数据
所有时间字段使用auto_now_add和auto_now
使用on_delete定义外键删除行为
使用unique_together定义联合唯一约束

你可以直接使用这些模型，只需要：

在settings.py中设置自定义用户模型：

AUTH_USER_MODEL = 'yourapp.User'

创建并应用迁移：

python manage.py makemigrations
python manage.py migrate

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

2026最新AI医疗行业全解析！小白/程序员入门AI赛道必看

DAMO开发者矩阵

告别传统热线！PCSwitch在线语音客服，让网页通话“零成本”成为现实

DAMO开发者矩阵

通用大模型、RAG与Agent在售前场景中怎么分工？别再把三者混成一个聊天机器人

企业建设AI售前助手时，常见误区是把通用大模型、RAG和Agent当成三个可以互相替代的方案。实际上，通用大模型负责语言理解与生成，RAG负责把内部知识送入上下文，Agent负责规划任务、调用工具和推动流程。本文围绕需求分析、产品匹配、方案生成、报价、投标和审批等售前任务，给出三类能力的边界、组合方式和选型建议。