[cloer chat] 데이터베이스 설계

 

 

데이터베이스 구조부터 잡고 sequelize와 MySQL query를 이용해 스키마를 생성해보자!

 

 

---

데이터베이스

 저는 서비스를 만들 때 기능들을 구상하고 인터페이스를 대충 생각 해보고 데이터 베이스를 설계하는 편입니다. 기능 구상은 저번 포스트에서 했고 인터페이스는 크게 신경 쓰지 않을 계획이니 바로 데이터베이스를 설계해 보겠습니다. 우선 채팅 서비스에 필요한 데이터부터 생각해보면 유저, 대화방, 메시지 정도가 있습니다. 

기본 정보

모든 테이블에 createdAt, updateAt, deletedAt을 추가 해 시간을 이용해 대화방에서 나갔다가 다시 접속했을 때 나가기 전 메시지를 불러오지 않을 계획입니다. 대충 컬럼을 생각해봤으니 관계를 맺고 정규화를 통해 나은 방법을 찾아보겠습니다.

 

 

---

pseudo ERD

'메시지를 어디에 연결할까?' 가장 큰 고민이었습니다. 가장 처음 든 생각은 <방법 1>입니다. 하지만 <방법 1>은 chats에서 한 명이 같은 방에 채팅을 보낼 때마다 userId와 roomId를 중복해서 담은 데이터가 chats 테이블에 쌓이게 됩니다. 또 chats와 users_rooms테이블에 데이터가 중복됩니다. 그래서 <방법 2>를 생각했습니다. uesrs와 rooms의 다대다 관계를 이어주는 users_rooms테이블에 chats를 연결해 데이터 중복을 줄였습니다.

pseudo ERD

 

 

---

 

sequelize 세팅

사실 sequelize를 사용할지 mysql 쿼리를 사용할지 아직 고민입니다. 그래서 sequelize를 이용한 세팅과 query를 이용한 세팅을 모두 해봤습니다.

 

modals/index.js

const Sequelize = require('sequelize');
const User = require('./user');
const Room = require('./room');
const UserRoom = require('./userRoom');
const Chat = require('./chat');

const env = process.env.NODE_ENV || 'development';
const config = require('../config/config')[env];
const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);
db.sequelize = sequelize;

db.User = User;
db.Room = Room;
db.UserRoom = UserRoom;
db.Chat = Chat;

User.init(sequelize);
Room.init(sequelize);
UserRoom.init(sequelize);
Chat.init(sequelize);

User.associate(db);
Room.associate(db);
UserRoom.associate(db);
Chat.associate(db);

module.exports = db;

modals/user.js

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
  static init(sequelize) {
    return super.init(
      {
        email: {
          type: Sequelize.STRING(40),
          allowNull: true,
          unique: true,
        },
        nickname: {
          type: Sequelize.STRING(15),
          allowNull: false,
          unique: true,
        },
        password: {
          type: Sequelize.STRING(100),
          allowNull: true,
        },
        provider: {
          type: Sequelize.STRING(10),
          allowNull: false,
          defaultValue: 'local',
        },
        snsId: {
          type: Sequelize.STRING(30),
          allowNull: true,
        },
      },
      {
        sequelize,
        timestamps: true,
        underscored: false,
        paranoid: true,
        modelName: 'User',
        tableName: 'users',
        charset: 'utf8',
        collate: 'utf8_general_ci',
      }
    );
  }
  static associate(db) {
    db.User.hasMany(db.Post);
    db.User.hasMany(db.Domain);
    db.User.belongsToMany(db.User, {
      foreignKey: 'followingId',
      as: 'Followers',
      through: 'Follow',
    });
    db.User.belongsToMany(db.User, {
      foreignKey: 'followerId',
      as: 'Followings',
      through: 'Follow',
    });
  }
};

modals/room.js

const Sequelize = require('sequelize');

module.exports = class Room extends Sequelize.Model {
  static init(sequelize) {
    return super.init(
      {
        title: {
          type: Sequelize.STRING(20),
          unique: true,
          allowNull: false,
        },
        password: {
          type: Sequelize.STRING(100),
          allowNull: true,
        },
      },
      {
        sequelize,
        timestamps: true,
        underscored: false,
        paranoid: true,
        modelName: 'User',
        tableName: 'users',
        charset: 'utf8mb4',
        collate: 'utf8mb4_general_ci',
      }
    );
  }
  static associate(db) {
    db.Room.hasMany(db.UserRoom);
  }
};

modals/userRoom.js

const Sequelize = require('sequelize');

module.exports = class UserRoom extends Sequelize.Model {
  static init(sequelize) {
    return super.init(
      {},
      {
        sequelize,
        timestamps: true,
        underscored: false,
        paranoid: true,
        modelName: 'UserRoom',
        tableName: 'users_rooms',
        charset: 'utf8',
        collate: 'utf8_general_ci',
      }
    );
  }
  static associate(db) {
    db.UserRoom.belongsTo(db.User);
    db.UserRoom.belongsTo(db.Room);
    db.UserRoom.hasMany(db.Chat);
  }
};

modals/chat.js

const Sequelize = require('sequelize');

module.exports = class Chat extends Sequelize.Model {
  static init(sequelize) {
    return super.init(
      {
        message: {
          type: Sequelize.STRING(400),
          allowNull: false,
        },
      },
      {
        sequelize,
        timestamps: true,
        underscored: false,
        paranoid: true,
        modelName: 'User',
        tableName: 'users',
        charset: 'utf8mb4',
        collate: 'utf8mb4_general_ci',
      }
    );
  }
  static associate(db) {
    db.Chat.belongsTo(db.UserRoom);
  }
};

 

 

---

 

MySQL query 세팅

CREATE DATABASE socket_io_chat ;
USE socket_io_chat;

CREATE TABLE Users(
    `userId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(40) NOT NULL UNIQUE ,
    `nickname` VARCHAR(10) NOT NULL UNIQUE ,
    `password` VARCHAR(100) NULL ,
    `provider` VARCHAR(6) NOT NULL DEFAULT 'local',
    `snsId` VARCHAR(30) NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `deletedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`userId`)
);
CREATE TABLE Rooms(
    `roomId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(40) NOT NULL UNIQUE ,
    `password` VARCHAR(100) NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `deletedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`roomId`)
);
CREATE TABLE Users_Rooms(
    `user_roomId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `userId` INT(10) UNSIGNED NOT NULL ,
    `roomId` INT(10) UNSIGNED NOT NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `deletedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`user_roomId`)
);
CREATE TABLE Chats(
    `chatId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `message` VARCHAR(400) NOT NULL ,
    `user_roomId` INT(10) UNSIGNED NOT NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `deletedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`chatId`)
);
ALTER TABLE `Users_Rooms` ADD CONSTRAINT `fk_Users_Rooms_userId` FOREIGN KEY(`userId`) 
REFERENCES `Users` (`userId`);
ALTER TABLE `Users_Rooms` ADD CONSTRAINT `fk_Users_Rooms_roomId` FOREIGN KEY(`roomId`) 
REFERENCES `Rooms` (`roomId`);
ALTER TABLE `Chats` ADD CONSTRAINT `fk_Chats_user_roomId` FOREIGN KEY(`user_roomId`) 
REFERENCES `Users_Rooms` (`user_roomId`);

쿼리를 작성한 이상 sequelize 대신 mysql2 모듈을 직접 사용하여 구현하기로 했습니다.

 

+ 수정(2022-03-24)

수정사항

1. 아마존 로그인 창을 참고하여 e-mail길이 변경 (30->64)
2. deletedAt컬럼 대신 status 컬럼 사용
3. room title을 UI에 맞게 24글자로 제한

-- Create Tables
CREATE TABLE Users(
    `userId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(64) NOT NULL UNIQUE ,
    `nickname` VARCHAR(10) NOT NULL UNIQUE ,
    `password` VARCHAR(100) NULL ,
    `provider` VARCHAR(6) NOT NULL DEFAULT 'local',
    `snsId` VARCHAR(30) NULL DEFAULT NULL,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `status` CHAR(1) DEFAULT 'a',
    PRIMARY KEY (`userId`)
);
CREATE TABLE Rooms(
    `roomId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(24) NOT NULL UNIQUE ,
    `password` VARCHAR(100) NULL DEFAULT NULL,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `status` CHAR(1) DEFAULT 'a',
    PRIMARY KEY (`roomId`)
);
CREATE TABLE Users_Rooms(
    `user_roomId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `userId` INT(10) UNSIGNED NOT NULL ,
    `roomId` INT(10) UNSIGNED NOT NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `status` CHAR(1) DEFAULT 'a',
    PRIMARY KEY (`user_roomId`)
);
CREATE TABLE Chats(
    `chatId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `message` VARCHAR(400) NOT NULL ,
    `user_roomId` INT(10) UNSIGNED NOT NULL ,
    `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    `updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `status` CHAR(1) DEFAULT 'a',
    PRIMARY KEY (`chatId`)
);
ALTER TABLE `Users_Rooms` ADD CONSTRAINT `fk_Users_Rooms_userId` FOREIGN KEY(`userId`) REFERENCES `Users` (`userId`);
ALTER TABLE `Users_Rooms` ADD CONSTRAINT `fk_Users_Rooms_roomId` FOREIGN KEY(`roomId`) REFERENCES `Rooms` (`roomId`);
ALTER TABLE `Chats` ADD CONSTRAINT `fk_Chats_user_roomId` FOREIGN KEY(`user_roomId`) REFERENCES `Users_Rooms` (`user_roomId`);