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?
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.
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.
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.
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.
Conversation has_may Lines
Line belongs_to User, has content & time
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With