张家豪 | 450410e | 2025-06-07 00:53:22 +0800 | [diff] [blame^] | 1 | --forums 表 – 论坛板块 |
| 2 | CREATE 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 表 – 帖子主题(一个帖子) |
| 14 | CREATE 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 表 – 帖子内容(主题 + 回复) |
| 29 | CREATE 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 表 – 帖子点赞记录 |
| 43 | CREATE 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 表 – 标签系统 |
| 53 | CREATE 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 |
| 61 | CREATE 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 表 – 浏览记录 |
| 70 | CREATE 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 表 – 用户关注(收藏)的帖子 |
| 80 | CREATE 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 | --用户论坛活动记录表 |
| 90 | CREATE 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 | -- |