blob: 040894a08061c83b6e55216ab777dcd1057a0125 [file] [log] [blame]
张家豪450410e2025-06-07 00:53:22 +08001--forums 表 – 论坛板块
2CREATE TABLE `forums` (
3 `id` BIGINT NOT NULL AUTO_INCREMENT,
4 `slug` VARCHAR(255) NOT NULL UNIQUE,
5 `name` VARCHAR(255) NOT NULL,
6 `description` TEXT,
7 `parent_id` BIGINT DEFAULT NULL,
8 `sort_order` INT DEFAULT 0,
9 `is_locked` BOOLEAN DEFAULT FALSE,
10 PRIMARY KEY (`id`),
11 FOREIGN KEY (`parent_id`) REFERENCES `forums`(`id`) ON DELETE SET NULL
12) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
13--topics 表 – 帖子主题(一个帖子)
14CREATE TABLE `topics` (
15 `id` BIGINT NOT NULL AUTO_INCREMENT,
16 `forum_id` BIGINT NOT NULL,
17 `user_id` BIGINT NOT NULL,
18 `title` VARCHAR(255) NOT NULL,
19 `is_pinned` BOOLEAN DEFAULT FALSE,
20 `is_locked` BOOLEAN DEFAULT FALSE,
21 `created_at` DATETIME NOT NULL,
22 `updated_at` DATETIME NOT NULL,
23 PRIMARY KEY (`id`),
24 FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON DELETE CASCADE,
25 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
26);
27
28--posts 表 – 帖子内容(主题 + 回复)
29CREATE TABLE `posts` (
30 `id` BIGINT NOT NULL AUTO_INCREMENT,
31 `topic_id` BIGINT NOT NULL,
32 `user_id` BIGINT NOT NULL,
33 `content` TEXT NOT NULL,
34 `created_at` DATETIME NOT NULL,
35 `updated_at` DATETIME NOT NULL,
36 `is_deleted` BOOLEAN DEFAULT FALSE,
37 PRIMARY KEY (`id`),
38 FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
39 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
40);
41
42--post_likes 表 – 帖子点赞记录
43CREATE TABLE `post_likes` (
44 `user_id` BIGINT NOT NULL,
45 `post_id` BIGINT NOT NULL,
46 `created_at` DATETIME NOT NULL,
47 PRIMARY KEY (`user_id`, `post_id`),
48 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
49 FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE
50);
51
52--forum_tags 表 – 标签系统
53CREATE TABLE `forum_tags` (
54 `id` BIGINT NOT NULL AUTO_INCREMENT,
55 `name` VARCHAR(50) NOT NULL UNIQUE,
56 `color` VARCHAR(10) DEFAULT '#ccc',
57 PRIMARY KEY (`id`)
58);
59
60--中间表:topic_tags
61CREATE TABLE `topic_tags` (
62 `topic_id` BIGINT NOT NULL,
63 `tag_id` BIGINT NOT NULL,
64 PRIMARY KEY (`topic_id`, `tag_id`),
65 FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
66 FOREIGN KEY (`tag_id`) REFERENCES `forum_tags`(`id`) ON DELETE CASCADE
67);
68
69--topic_views 表 – 浏览记录
70CREATE TABLE `topic_views` (
71 `topic_id` BIGINT NOT NULL,
72 `user_id` BIGINT NOT NULL,
73 `last_viewed_at` DATETIME NOT NULL,
74 PRIMARY KEY (`topic_id`, `user_id`),
75 FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
76 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
77);
78
79--topic_subscriptions 表 – 用户关注(收藏)的帖子
80CREATE TABLE `topic_subscriptions` (
81 `user_id` BIGINT NOT NULL,
82 `topic_id` BIGINT NOT NULL,
83 `subscribed_at` DATETIME NOT NULL,
84 PRIMARY KEY (`user_id`, `topic_id`),
85 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
86 FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE
87);
88
89--用户论坛活动记录表
90CREATE TABLE `user_forum_history` (
91 `id` BIGINT NOT NULL AUTO_INCREMENT,
92 `user_id` BIGINT NOT NULL,
93 `type` ENUM('topic', 'reply') NOT NULL COMMENT '操作类型:发主题或回复',
94 `target_id` BIGINT NOT NULL COMMENT 'topic_id 或 post_id',
95 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
96 PRIMARY KEY (`id`),
97 INDEX `idx_user_id` (`user_id`),
98 INDEX `idx_type_target` (`type`, `target_id`),
99 CONSTRAINT `fk_ufh_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
100) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
101
102--