DB Schema For Chats?

Question

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?

anonymous chats or recognized users? – Peter Bailey Jun 22 '10 at 15:29 — Peter Bailey, Jun 22 '10 at 15:29

score 40 · Accepted Answer · edited May 14 '22 at 17:38

Here's a start using 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.

Nope, I still get a 404 on link. – Will Morgan Feb 09 '12 at 16:01 — Will Morgan, Feb 09 '12 at 16:01

score 5 · Answer 2 · answered Jun 22 '10 at 15:16

5

Conversation has_may Lines

Line belongs_to User, has content & time

answered Jun 22 '10 at 15:16

thomasfedb

5,990
2
37
65

This sounds so DB intensive. You think if you have 10K users chatting everyday, you're going to be swamped in data within a month.. – Trip Jan 22 '16 at 22:14
4

30 days * 1440 minutes in a day * 200 characters per minute (average typing speed) * 10,000 users chatting = 85 gigabytes (roughly) – thomasfedb Jan 24 '16 at 15:33
1

Of course, this assumes that your users literally devote 24/7 to chatting... In any case, 85GB is probably well within what most SQL databases can handle, but perhaps you could consider purging conversation lines older than 24 hours, etc. – thomasfedb Jan 24 '16 at 15:35
5

Purging might be a good idea. Or maybe taking entire days and concatenating them into single files. So every day gets one big text. – Trip Jan 25 '16 at 15:37

DB Schema For Chats?

2 Answers2

Linked