CRM Database Structure, MySQL Schema & Wireframe

Step 1 – Create basicCRM database

DROP SCHEMA IF EXISTS `basicCRM` ;

CREATE SCHEMA IF NOT EXISTS `basicCRM` DEFAULT CHARACTER SET utf8 ;

USE `basicCRM` ;

Step 2 – Create users table

DROP TABLE IF EXISTS `basicCRM`.`users` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `fullName` VARCHAR(200) NULL,
  `email` VARCHAR(45) NULL,
  `username` VARCHAR(45) NULL,
  `phone` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `userType` ENUM('admin', 'subadmin', 'subscriber') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

Step 3 – Create services table

DROP TABLE IF EXISTS `basicCRM`.`services` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`services` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `price` INT NULL,
  `user_id` INT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `user_id_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `basicCRM`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 4 – Create clients table

DROP TABLE IF EXISTS `basicCRM`.`clients` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`clients` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `fullName` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `phone` VARCHAR(45) NULL,
  `address` VARCHAR(200) NULL,
  `taxId` VARCHAR(45) NULL,
  `source` VARCHAR(200) NULL,
  `notes` VARCHAR(45) NULL,
  `user_id` INT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `user_id_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `basicCRM`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 5 – Create lead table

DROP TABLE IF EXISTS `basicCRM`.`lead` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`lead` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `client_id` VARCHAR(45) NULL,
  `requirement` TEXT(20000) NULL,
  `estimatedBudget` VARCHAR(45) NULL,
  `user_id` INT NULL,
  `startDate` DATETIME NULL,
  `deadline` DATETIME NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `status` ENUM('In Progress', 'Order Generated', 'On Hold', 'Cancelled', 'Not Eligible') NULL,
  PRIMARY KEY (`id`),
  INDEX `user_id_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `basicCRM`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `client_id`
    FOREIGN KEY ()
    REFERENCES `basicCRM`.`clients` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 6 – Create leadDetails table

DROP TABLE IF EXISTS `basicCRM`.`leadDetails` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`leadDetails` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `lead_id` INT NULL,
  `services_id` INT NULL,
  `quantity` INT NULL,
  `notes` TEXT(2000) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `lead_id_idx` (`lead_id` ASC) VISIBLE,
  CONSTRAINT `services_id`
    FOREIGN KEY ()
    REFERENCES `basicCRM`.`services` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `lead_id`
    FOREIGN KEY (`lead_id`)
    REFERENCES `basicCRM`.`lead` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 7 – Create statusUpdates table

DROP TABLE IF EXISTS `basicCRM`.`statusUpdates` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`statusUpdates` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `lead_id` INT NULL,
  `notes` TEXT(2000) NULL,
  `status` VARCHAR(45) NULL,
  `user_id` INT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `lead_id`
    FOREIGN KEY ()
    REFERENCES `basicCRM`.`lead` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `basicCRM`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 8 – Create todo table

DROP TABLE IF EXISTS `basicCRM`.`todo` ;

CREATE TABLE IF NOT EXISTS `basicCRM`.`todo` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` TEXT(500) NULL,
  `description` TEXT(2000) NULL,
  `dueDate` VARCHAR(45) NULL,
  `status` ENUM("Pending", "Complete") NULL,
  `user_id` INT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `lead_id` INT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `basicCRM`.`users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

---

CRM Wireframe