article thumbnail image
Published 2021. 10. 2. 16:49

YouTube 클론 프로젝트 - Product App(YouTube) 데이터 모델링 및 ERD 구축

YouTube Clone Project
728x90

 

 

📌  데이터 모델링이란?

 

우리는 일상생활에서 모델을 많이 접하고 있다. 예술 분야의 작품의 대상과 건축 분야의 설계도 등을 우리는 모델이라고 일컫는다. 이와 같이 모델이란 어떤 대상을 의미하는 포괄적 의미를 가지고 있으며, 특히 데이터 모델은 현실 세계에 대한 우리가 관심 있는 대상을 데이터베이스 화하기 위한 개념적 도구라고 정의할 수 있다.

 

 

 

 

📌  데이터 모델링 단계

 

  • 개념 데이터 모델링 : 주제별로 분류 가능한 업무를 분석해서, 핵심 엔터티를 추출하고 그들 간의 관계를 정의하여 데이터 전체 골격을 생성한다.
  • 논리 데이터 모델링 : 핵심 엔터티와 관계를 바탕으로 상세 속성을 정의하고 식별자를 확정하며, 정규화와 같은 상세화 과정을 수행한다.
  • 물리 데이터 모델링 : DBMS의 특성 및 구현 환경 등을 감안한 스키마를 일정한 기준과 규칙에 의해 도출하고 칼럼의 데이터 타입과 크기를 정의한다.




 

 

 

 

 

 

📖   관계 파악

실제 운영되고 우리가 가장 많이 접하는 애플리케이션 중 하나인 YouTube를 선정해서 DB설계를 해보자

우선 ERD를 구축하기 전에 관계 설정을 해야 한다.

ERD의 cardinality, optionality 관계를 파악할 때 헷갈림을 줄여주기 위해 생활코딩님께서 만든 교육용 앱을 이용했다.

http://erd.yah.ac/

 

 

 

 

 

 

User와 Channel의 관계 (N:M 관계)

 

  • User는 다수의 Channel에 때때로 구독된다.
  • Chaneel은 다수의 User에 때때로 구독된다.

이 경우는 N:M 매핑으로 중간에 FK를 2개 갖는 맵핑 테이블이 필요하다.

이때 User와 Channel 사이에 Subscribe(구독)이라는 관계로 매핑시켜주었다.

 

 

 

 

 

 

 

Channel과 Vidio의 관계 (1:N 관계)

 

  • Vidio는 하나의 Channel에 반드시 소유된다.
  • Channel은 다수의 Vidio를 때때로 소유한다.

 

 

 

 

 

 

 

User와 YouTubePremium의 관계 (1:1 관계) 

 

1:1 관계일 때는 의존성을 파악해야 한다.

YouTubePremium은 User에 의존한다. User가 없이 YouTubePremium가 존재할 수 없기 때문이다.

  • User는 하나의 YouTubePremium을 때때로 소유한다.
  • YouTubePremium은 하나의 User에 반드시 소유된다. (YouTubePremium이 존재한다면 하나의 User에는 반드시 소유된다.)

 

 

 

 

 

 

 

Channel과 Community의 관계 (1:1 관계)

 

마찬가지로 의존성을 파악한다.

Community는 Channle 없이 존재할 수 없다.

  • Channel은 다수의 Community에 때때로 포함된다.
  • Community는 하나의 Channle에 반드시 포함된다. (Community이 존재한다면 하나의 Channle에는 반드시 소유된다.)

 

 

 

 

 

 

 

 

 

 

 

 

이와 같이 각 관계를 파악하고 이를 이용해 ERD를 구축했다.

 

 

📝  YouTube ERD (AqueryTool 사용)

 

 

피드백을 통한 개선사항

  • CREATED_AT, UPDATED_AT, STATUS 모든 테이블에 추가. 
  • CREATED_AT 에는 디폴트 값 CURRENT_TIMESTAMP , UPDATED_AT에는 디폴트 값 current_timestamp on update current_timestamp를 추가함으로써 자동으로 현재 시각으로 업데이트될 수 있도록 함
  • 용도에 맞게 데이터 타입을 변경
  • 좋아요(video, community)의 경우, 누가 좋아요를 눌렀는지 알아야 하기 때문에 추가적인 FK 생성
  • 연결 테이블의 PK의 경우, 복합 키(2개의 PK)가 아닌 별도의 독립적인 키를 만드는 것이 좋다 하여 별도의 PK생성
  • 진행해 나아가며 지속해서 부족한 테이블을 추가
  • ERD를 완성시키고 모의 데이터를 넣을 때 FK형식이 맞지 않다거나 관계를 잘못 생각한 테이블들 수정

 

 

 

 

 

 

AqueryTool은 내부 기능으로 ERD를 쉽게 SQL문을 뽑아낼 수 있었다.

ERD to SQL

CREATE DATABASE YouTube;
USE YouTube;


CREATE TABLE Channel
(
    `channelId`            INT            NOT NULL    AUTO_INCREMENT, 
    `channelName`          VARCHAR(45)    NOT NULL, 
    `channelProfileImage`  TEXT           NULL, 
    `status`               VARCHAR(10)    NOT NULL, 
    `createdAt`            TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`             TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_Channel PRIMARY KEY (channelId)
);


-- Vidio Table Create SQL
CREATE TABLE Vidio
(
    `vidioId`    INT            NOT NULL    AUTO_INCREMENT, 
    `thumbNail`  TEXT           NOT NULL, 
    `vidioName`  VARCHAR(45)    NOT NULL, 
    `createdAt`  TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`   TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    `status`     VARCHAR(10)    NOT NULL, 
    `channelId`  INT            NOT NULL, 
    `vidio`      TEXT           NOT NULL, 
    CONSTRAINT PK_Vidio PRIMARY KEY (vidioId)
);

ALTER TABLE Vidio
    ADD CONSTRAINT FK_Vidio_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- User Table Create SQL
CREATE TABLE User
(
    `userId`            INT             NOT NULL    AUTO_INCREMENT COMMENT 'user_id', 
    `userNickname`      VARCHAR(45)     NOT NULL    COMMENT 'user_nickname', 
    `userPassword`      VARCHAR(200)    NOT NULL    COMMENT 'user_email', 
    `userEmail`         VARCHAR(45)     NOT NULL, 
    `status`            VARCHAR(10)     NOT NULL, 
    `createdAt`         TIMESTAMP       NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`          TIMESTAMP       NULL        DEFAULT current_timestamp on update current_timestamp, 
    `userProfileImage`  TEXT            NULL, 
    `channelId`         INT             NOT NULL, 
    CONSTRAINT PK_User PRIMARY KEY (userId)
);

ALTER TABLE User
    ADD CONSTRAINT FK_User_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- VidioComment Table Create SQL
CREATE TABLE VidioComment
(
    `vidioCommentId`  INT            NOT NULL    AUTO_INCREMENT, 
    `vidioComment`    VARCHAR(45)    NOT NULL, 
    `status`          VARCHAR(10)    NOT NULL, 
    `createdAt`       TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`        TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    `channelId`       INT            NOT NULL, 
    `vidioId`         INT            NOT NULL, 
    `userId`          INT            NULL, 
    CONSTRAINT PK_Vidio_comment PRIMARY KEY (vidioCommentId)
);

ALTER TABLE VidioComment
    ADD CONSTRAINT FK_VidioComment_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioComment
    ADD CONSTRAINT FK_VidioComment_vidioId_Vidio_vidioId FOREIGN KEY (vidioId)
        REFERENCES Vidio (vidioId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioComment
    ADD CONSTRAINT FK_VidioComment_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Community Table Create SQL
CREATE TABLE Community
(
    `communityId`        INT            NOT NULL    AUTO_INCREMENT, 
    `communityMainText`  VARCHAR(45)    NULL, 
    `communityComment`   VARCHAR(45)    NULL, 
    `channelId`          INT            NOT NULL, 
    `status`             VARCHAR(10)    NULL, 
    `createdAt`          TIMESTAMP      NULL        DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`           TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_Community_id PRIMARY KEY (communityId)
);

ALTER TABLE Community
    ADD CONSTRAINT FK_Community_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- ChannelLanguage Table Create SQL
CREATE TABLE ChannelLanguage
(
    `CLId`       INT            NOT NULL    AUTO_INCREMENT, 
    `Korean`     VARCHAR(10)    NULL, 
    `English`    VARCHAR(10)    NULL, 
    `Japanese`   VARCHAR(10)    NULL, 
    `Chinese`    VARCHAR(10)    NULL, 
    `status`     VARCHAR(10)    NULL, 
    `createdAt`  TIMESTAMP      NULL        DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`   TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_Channel_language PRIMARY KEY (CLId)
);


-- Subscribe Table Create SQL
CREATE TABLE Subscribe
(
    `subscribeId`  INT            NOT NULL    AUTO_INCREMENT COMMENT 'user_id', 
    `createdAt`    TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`     TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    `status`       VARCHAR(10)    NOT NULL, 
    `userId`       INT            NOT NULL, 
    `channelId`    INT            NOT NULL, 
    CONSTRAINT PK_Subscribe PRIMARY KEY (subscribeId)
);

ALTER TABLE Subscribe
    ADD CONSTRAINT FK_Subscribe_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE Subscribe
    ADD CONSTRAINT FK_Subscribe_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- VidioLike Table Create SQL
CREATE TABLE VidioLike
(
    `vidioLikeId`  INT            NOT NULL    AUTO_INCREMENT, 
    `vidioId`      INT            NOT NULL, 
    `userid`       INT            NOT NULL, 
    `createdAt`    TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`     TIMESTAMP      NOT NULL    DEFAULT current_timestamp on update current_timestamp, 
    `status`       VARCHAR(10)    NULL, 
    CONSTRAINT PK_Vidio_like PRIMARY KEY (vidioLikeId)
);

ALTER TABLE VidioLike
    ADD CONSTRAINT FK_VidioLike_vidioId_Vidio_vidioId FOREIGN KEY (vidioId)
        REFERENCES Vidio (vidioId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioLike
    ADD CONSTRAINT FK_VidioLike_userid_User_userId FOREIGN KEY (userid)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- CommunityLike Table Create SQL
CREATE TABLE CommunityLike
(
    `communityLikeId`  INT            NOT NULL    AUTO_INCREMENT, 
    `communityId`      INT            NOT NULL, 
    `channelId`        INT            NOT NULL, 
    `userId`           INT            NOT NULL, 
    `status`           VARCHAR(10)    NOT NULL, 
    `createdAt`        TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`         TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_Community_like PRIMARY KEY (communityLikeId)
);

ALTER TABLE CommunityLike
    ADD CONSTRAINT FK_CommunityLike_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE CommunityLike
    ADD CONSTRAINT FK_CommunityLike_communityId_Community_communityId FOREIGN KEY (communityId)
        REFERENCES Community (communityId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE CommunityLike
    ADD CONSTRAINT FK_CommunityLike_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- ChannelInformation Table Create SQL
CREATE TABLE ChannelInformation
(
    `channelInformationId`  INT             NOT NULL    AUTO_INCREMENT, 
    `information`           VARCHAR(100)    NULL, 
    `createdAt`             TIMESTAMP       NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`              TIMESTAMP       NULL        DEFAULT current_timestamp on update current_timestamp, 
    `status`                VARCHAR(10)     NOT NULL, 
    `channelId`             INT             NOT NULL, 
    CONSTRAINT PK_Channel_information PRIMARY KEY (channelInformationId)
);

ALTER TABLE ChannelInformation
    ADD CONSTRAINT FK_ChannelInformation_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- YouTubePrimium Table Create SQL
CREATE TABLE YouTubePrimium
(
    `youtubePremiumId`  INT            NOT NULL    AUTO_INCREMENT, 
    `createdAt`         TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`          TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    `status`            VARCHAR(10)    NOT NULL, 
    `userId`            INT            NOT NULL, 
    CONSTRAINT PK_YouTube_Primium PRIMARY KEY (youtubePremiumId)
);

ALTER TABLE YouTubePrimium
    ADD CONSTRAINT FK_YouTubePrimium_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- Category Table Create SQL
CREATE TABLE Category
(
    `categoryId`       INT            NOT NULL    AUTO_INCREMENT, 
    `vidioId`          INT            NOT NULL, 
    `categoryContent`  VARCHAR(10)    NULL, 
    `createdAt`        TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`         TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    `status`           VARCHAR(10)    NOT NULL, 
    CONSTRAINT PK_Category PRIMARY KEY (categoryId)
);

ALTER TABLE Category
    ADD CONSTRAINT FK_Category_vidioId_Vidio_vidioId FOREIGN KEY (vidioId)
        REFERENCES Vidio (vidioId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- CLMapping Table Create SQL
CREATE TABLE CLMapping
(
    `CLMappingId`  INT            NOT NULL, 
    `channelId`    INT            NOT NULL, 
    `CLId`         INT            NOT NULL, 
    `status`       VARCHAR(10)    NULL, 
    `createdAt`    TIMESTAMP      NULL        DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`     TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_Channel_language_mapping PRIMARY KEY (CLMappingId)
);

ALTER TABLE CLMapping
    ADD CONSTRAINT FK_CLMapping_CLId_ChannelLanguage_CLId FOREIGN KEY (CLId)
        REFERENCES ChannelLanguage (CLId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE CLMapping
    ADD CONSTRAINT FK_CLMapping_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- VidioUnLike Table Create SQL
CREATE TABLE VidioUnLike
(
    `vidioUnLikeId`  INT            NOT NULL    AUTO_INCREMENT, 
    `vidioId`        INT            NOT NULL, 
    `userId`         INT            NOT NULL, 
    `createdAt`      TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`       TIMESTAMP      NOT NULL    DEFAULT current_timestamp on update current_timestamp, 
    `status`         VARCHAR(10)    NULL, 
    CONSTRAINT PK_VidioUnLike PRIMARY KEY (vidioUnLikeId)
);

ALTER TABLE VidioUnLike
    ADD CONSTRAINT FK_VidioUnLike_vidioId_Vidio_vidioId FOREIGN KEY (vidioId)
        REFERENCES Vidio (vidioId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioUnLike
    ADD CONSTRAINT FK_VidioUnLike_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;


-- VidioCommentLike Table Create SQL
CREATE TABLE VidioCommentLike
(
    `vidioCommentLikeId`  INT            NOT NULL    AUTO_INCREMENT, 
    `vidioCommentId`      INT            NOT NULL, 
    `userId`              INT            NOT NULL, 
    `channelId`           INT            NOT NULL, 
    `status`              VARCHAR(10)    NOT NULL, 
    `createdAt`           TIMESTAMP      NOT NULL    DEFAULT CURRENT_TIMESTAMP, 
    `updateAt`            TIMESTAMP      NULL        DEFAULT current_timestamp on update current_timestamp, 
    CONSTRAINT PK_VidioCommentLike PRIMARY KEY (vidioCommentLikeId)
);

ALTER TABLE VidioCommentLike
    ADD CONSTRAINT FK_VidioCommentLike_vidioCommentId_VidioComment_vidioCommentId FOREIGN KEY (vidioCommentId)
        REFERENCES VidioComment (vidioCommentId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioCommentLike
    ADD CONSTRAINT FK_VidioCommentLike_userId_User_userId FOREIGN KEY (userId)
        REFERENCES User (userId) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE VidioCommentLike
    ADD CONSTRAINT FK_VidioCommentLike_channelId_Channel_channelId FOREIGN KEY (channelId)
        REFERENCES Channel (channelId) ON DELETE RESTRICT ON UPDATE RESTRICT;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Reference:

http://erd.yah.ac/

https://augustines.tistory.com/50

'YouTube Clone Project' 카테고리의 다른 글

YouTube 클론 프로젝트 - OAuth를 이용한 카카오 로그인 구현 (Spring Boot)  (0) 2021.10.29
YouTube 클론 프로젝트 - JWT를 이용한 로그인 인증처리 (Spring Boot)  (0) 2021.10.18
YouTube 클론 프로젝트 - API 리스트업 및 기능 구현 (Spring Boot)  (1) 2021.10.11
YouTube 클론 프로젝트 - Product App(YouTube) 한 방 쿼리 작성  (0) 2021.10.02
복사했습니다!

티스토리툴바