Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DB Schema For Chats?

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?

like image 470
Alex Gordon Avatar asked Jun 22 '10 15:06

Alex Gordon


People also ask

Which DB is best for chat application?

I think it's hard to say that one database or another is the BEST without understanding more about the application but rest assured that MongoDB has been the choice for many popular chat applications.

How are chat messages stored in database?

You can store message by message in the DB having send to, send from and date time like fields, meaning one message per record. Or you can also save session based message history at once per record, and it may contain several messages per record.

What database is used for Messenger?

Now the messenger service uses RocksDB to store user messages. HBase is also used in production by other services such as the internal monitoring system, search indexing, streaming data analysis & data scraping.


2 Answers

Here's a start using MySQL Workbench

PNG export of ERD

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.

like image 76
Peter Bailey Avatar answered Sep 29 '22 02:09

Peter Bailey


Conversation has_may Lines

Line belongs_to User, has content & time

like image 20
thomasfedb Avatar answered Sep 29 '22 03:09

thomasfedb