CODEWITHSUNDEEP
phpmysqldatabase-designmessagedatabase-schema
Raf
Raf

Reputation: 708

Messaging system flow into database

Simple or Personal type messaging :

I tried to follow the other answers here but can't understand how they are really saved into the database enter image description here

Sender:
Sends message and saved/tag under "Sent" message

Receiver:
Receives message and tag as "Inbox" message

My problem: When I tried to send a message and update my field "Placeholder" to "Sent", the receiver coudn't see it in his "Inbox" because it was already set to "Sent".

btw Placeholder is like a message status(Inbox, Sent, Trash), I created 3 custom views displayed in Tab form for my joined tables (Inbox, Sent and Trash)

Question:

  1. Do I have to insert 2 records everytime a message was sent? (Sent and Inbox)
  2. Is that the best database schema to follow if I only need a Simple/personal message?

Upvotes: 2

Views: 1259

Answers (1)

user1032531
user1032531

Reputation: 26311

  1. Do I have to insert 2 records everytime a message was sent? (Sent and Inbox)

No, just one time.

  1. Is that the best database schema to follow if I only need a Simple/personal message?

I would recommend keeping it very simple. Note that "Sent_two" should obviously be "sent_to". This will do everything you need with the possible exception of the place holder part which I currently don't understand your intent.

enter image description here

-- MySQL Script generated by MySQL Workbench
-- 11/30/14 09:07:59
-- Model: New Model    Version: 1.0
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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
  `idUsers` INT NOT NULL,
  `firstname` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  `username` VARCHAR(45) NULL,
  PRIMARY KEY (`idUsers`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`messages`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`messages` (
  `idmessages` INT NOT NULL,
  `Subject` VARCHAR(45) NULL,
  `Body` TEXT NULL,
  `Date` DATETIME NULL,
  `sent_from` INT NOT NULL,
  `sent_two` INT NOT NULL,
  PRIMARY KEY (`idmessages`),
  INDEX `fk_messages_Users_idx` (`sent_from` ASC),
  INDEX `fk_messages_Users1_idx` (`sent_two` ASC),
  CONSTRAINT `fk_messages_Users`
    FOREIGN KEY (`sent_from`)
    REFERENCES `mydb`.`Users` (`idUsers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_messages_Users1`
    FOREIGN KEY (`sent_two`)
    REFERENCES `mydb`.`Users` (`idUsers`)
    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;

Upvotes: 3

Related Questions