排行网站用户交互相关表设计
以下是排行网站中与用户交互相关的完整数据库表设计方案,包含用户行为、收藏、评论、评分等核心功能:
1. 用户基础表 (users)
sql
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar() NOT NULL COMMENT '用户名', `email` varchar(100) NOT NULL COMMENT '邮箱', `password_hash` varchar(255) NOT NULL COMMENT '密码哈希', `avatar` varchar(512) DEFAULT NULL COMMENT '头像URL', `bio` varchar(512) DEFAULT NULL COMMENT '个人简介', `last_login` datetime DEFAULT NULL COMMENT '最后登录时间', `status` tinyint(1) DEFAULT 1 COMMENT '1-正常 0-禁用', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_username` (`username`), UNIQUE KEY `uq_email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
2. 用户收藏表 (user_favorites)
sql
CREATE TABLE `user_favorites` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `media_id` int(11) NOT NULL COMMENT '媒体ID', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_user_media` (`user_id`,`media_id`), KEY `idx_media` (`media_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户收藏表';
3. 用户播放历史表 (user_play_history)
sql
CREATE TABLE `user_play_history` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `media_id` int(11) NOT NULL COMMENT '媒体ID', `play_time` datetime NOT NULL COMMENT '播放时间', `duration_played` int(11) DEFAULT NULL COMMENT '播放时长(秒)', `progress_percent` tinyint(3) DEFAULT NULL COMMENT '播放进度百分比', `device_type` varchar(50) DEFAULT NULL COMMENT '设备类型(mobile,pc,tablet等)', PRIMARY KEY (`id`), KEY `idx_user_time` (`user_id`,`play_time`), KEY `idx_media` (`media_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户播放历史表';
4. 用户评论表 (user_comments)
sql
CREATE TABLE `user_comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `media_id` int(11) NOT NULL COMMENT '媒体ID', `content` text NOT NULL COMMENT '评论内容', `parent_id` int(11) DEFAULT NULL COMMENT '父评论ID(回复用)', `likes` int(11) DEFAULT 0 COMMENT '点赞数', `is_featured` tinyint(1) DEFAULT 0 COMMENT '是否精选评论', `status` tinyint(1) DEFAULT 1 COMMENT '1-显示 0-隐藏', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_user_media` (`user_id`,`media_id`), KEY `idx_media` (`media_id`), KEY `idx_parent` (`parent_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户评论表';
5. 用户评分表 (user_ratings)
sql
CREATE TABLE `user_ratings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `media_id` int(11) NOT NULL COMMENT '媒体ID', `rating` tinyint(1) NOT NULL COMMENT '评分(1-5星)', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_user_media` (`user_id`,`media_id`), KEY `idx_media` (`media_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户评分表';
6. 用户关注表 (user_follows)
sql
CREATE TABLE `user_follows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `follower_id` int(11) NOT NULL COMMENT '关注者ID', `following_id` int(11) NOT NULL COMMENT '被关注者ID', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_follow_relation` (`follower_id`,`following_id`), KEY `idx_following` (`following_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户关注关系表';
7. 用户举报表 (user_reports)
sql
CREATE TABLE `user_reports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '举报用户ID', `target_type` enum('media','comment','user') NOT NULL COMMENT '举报目标类型', `target_id` int(11) NOT NULL COMMENT '举报目标ID', `reason` varchar(255) NOT NULL COMMENT '举报原因', `description` text DEFAULT NULL COMMENT '详细描述', `status` tinyint(1) DEFAULT 0 COMMENT '0-待处理 1-已处理', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_user` (`user_id`), KEY `idx_target` (`target_type`,`target_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户举报表';
8. 用户通知表 (user_notifications)
sql
CREATE TABLE `user_notifications` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '接收用户ID', `type` varchar(50) NOT NULL COMMENT '通知类型(like,comment,follow等)', `content` varchar(512) NOT NULL COMMENT '通知内容', `is_read` tinyint(1) DEFAULT 0 COMMENT '是否已读', `related_id` int(11) DEFAULT NULL COMMENT '关联ID(如评论ID)', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_user` (`user_id`), KEY `idx_type
特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。
Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.