MySQL 聊天的数据库架构？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3094495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB Schema For Chats?
提问by l--''''''---------''''''''''''
I need to store chat conversations in a database schema. The way I would use this database is I would post chats on a website. Each chat would not be more than about 20 responses. Can someone please suggest a schema for this?
我需要将聊天对话存储在数据库模式中。我使用这个数据库的方式是在网站上发布聊天记录。每次聊天不会超过大约 20 个回复。有人可以为此建议一个架构吗？
采纳答案by Peter Bailey
Here's a start using MySQL Workbench
这是使用MySQL Workbench的开始
And the create script
和创建脚本
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `chats` DEFAULT CHARACTER SET utf8 COLLATE default collation ;
-- -----------------------------------------------------
-- Table `chats`.`chat`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `chats`.`chat` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `chats`.`chat_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `chats`.`chat_user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`handle` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `chats`.`chat_line`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `chats`.`chat_line` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`chat_id` INT UNSIGNED NOT NULL ,
`user_id` INT UNSIGNED NOT NULL ,
`line_text` TEXT NOT NULL ,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) ,
INDEX `fk_chat_line_chat` (`chat_id` ASC) ,
INDEX `fk_chat_line_chat_user1` (`user_id` ASC) ,
CONSTRAINT `fk_chat_line_chat`
FOREIGN KEY (`chat_id` )
REFERENCES `chats`.`chat` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_chat_line_chat_user1`
FOREIGN KEY (`user_id` )
REFERENCES `chats`.`chat_user` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
And you are welcome to download the MWB file from my dropbox.
欢迎您从我的保管箱下载 MWB 文件。
回答by thomasfedb
Conversation has_may Lines
对话 has_may 行
Line belongs_to User, has content & time
行属于用户，有内容和时间
