[TOY] 설계 - 데이터베이스 DataBase

반응형

728x90

1. DateBase 

이번 프로젝트에서 사용하게 될 데이터베이스는 MySQL입니다. 스프링의 ORM 기술인 JPA를 사용한 프로젝트를 진행하여 관계형 데이터베이스를 사용하게 되었고 많은 온라인 리소스를 제공하는 오픈 소스 MySQL을 선택하였습니다. 

2. 테이블 & 다이어그램

앞선 분석과 설계로 뽑아낸 엔티티는 user, post, comment로 다이어그램과 테이블을 설계하였습니다.

2.1 ERD (Entity-Relationship Diagram)

아래는 구성된 다이어그램입니다.

ERD

엔티티가 몇 개 없는 간단한 프로젝트다 보니 다이어그램도 간결하게 구성되었습니다. 뽑아낸 엔티티에서 photos 테이블이 추가로 생성되었습니다. photos 테이블은 게시글에 이미지와 같은 파일이 포함될 경우 별도 처리를 위한 테이블입니다. comments와 photos 모두 posts 테이블이 존재하지 않으면 단독으로 존재 불가능한 약한 개체로 post는 필수로 1개 존재하고 photos , comments는 0개 또는 여러 개가 존재 가능한 1 Mandatory to Many Optional 관계입니다. 나머지의 경우 유저는 게시글과 댓글이 없을 수 도 있고 여러 개 일 수 도 있으니 각각 0 to Many Optional 관계를 가집니다. 

2.2 테이블 구성 & 제약 조건

각 테이블들의 상세 구성과 제약 조건입니다. 모든 테이블의 기본키는 Surrogate Key를 사용하고 각 테이블은 Auditing을 위한 created_at, updated_at 필드를 가집니다.

users
제약 조건	속성	타입
PK	user_id	BIGINT
UNIQUE, NOT NULL	login_id	VARCHAR(255)
NOT NULL	password	VARCHAR(255)
	username	VARCHAR(255)
UNIQUE	email	VARCHAR(255)
DEFAULT CURRENT_TIMESTAMP	created_at	TIMESTAMP
DEFAULT CURRENT_TIMESTAMP	updated_at	TIMESTAMP
DEFAULT 'USER'	role	ENUM('USER', 'MANAGER', 'ADMIN')

users 테이블입니다. login_id는 로그인 시 필요한 사용자 아이디로 Unique 제약 조건과 Not Null 제약 조건을 가집니다. email 또한 Unique 제약 조건을 가지나 필수 사항은 아니기에 Not Null 제약을 가지진 않습니다. user 또한 가입 일자와 정보가 수정된 일자를 기록하는 속성을 가지고 있으며 데이터 생성 시점을 기본값으로 가집니다. 유저가 가지고 있는 역할 속성으로 일반 회원, 매니저, 관리자를 구분하기 위해 사용됩니다. 데이터 타입으로 ENUM 타입을 사용하며 일반 회원을 기본 값으로 가집니다. 

posts
제약 조건	속성	타입
PK	post_id	BIGINT
NOT NULL	title	VARCHAR(255)
NOT NULL	content	TEXT
	diplay_name	VARCHAR(255)
	password	VARCHAR(255)
DEFAULT 0	recom_count	INT
DEFAULT 0	not_recom_count	INT
DEFAULT CURRENT_TIMESTAMP	created_at	TIMESTAMP
DEFAULT CURRENT_TIMESTAMP	updated_at	TIMESTAMP
DEFAULT 'NORMAL'	post_type	ENUM('NORMAL', 'NOTICE', 'POPULAR')
FK	user_id	BIGINT

posts 테이블의 구성입니다. 제목과 게시글 내용 속성은 Not Null로 필수적으로 존재해야 합니다. 게시글 내용의 경우 text 타입을 사용하여 긴 내용의 문자도 저장할 수 있게 하였습니다. 비회원을 위한 표시 이름, 비밀 번호 속성을 가지고 있습니다. 조회 성능을 위해 비회원에 관한 내용을 별도의 테이블로 분리하지 않고 회원인 경우 해당 속성 값은 비어있게 됩니다. 추천수와 비추천 수는 초기값 0으로 생성됩니다. 게시글 타입은 일반 게시글, 매니저 이상이 작성 가능한 공지, 일정 추천수 이상일 경우 인기글을 구분하기 위한 ENUM 타입을 사용했습니다. 단순 카테고리 분류를 위함이고 각 게시글들의 차이는 없기에 추가 테이블 없이 단순하게  ENUM 타입으로 구분해 줬습니다. 마지막으로 회원이 작성한 경우 작성한 회원의 아이디를 외래키로 가집니다. 

Photos
제약 조건	속성	타입
PK	photo_id	BIGINT
NOT NULL	original_photo _name	VARCHAR(255)
NOT NULL	saved_photo _name	VARCHAR(255)
NOT NULL	photo _path	VARCHAR(255)
DEFAULT CURRENT_TIMESTAMP	uploded_at	TIMESTAMP
FK, NOT NULL, ON DELETE CASCADE	post_id	BIGINT

photos 테이블 구성입니다. 저장소에 저장될 파일의 이름과 경로는 Not Null 제약을 가집니다. 게시글의 아이디를 외래키로 가지고 있는데 photos는 약한 개체로 강한 개체인 post의 아이디 값이 필수로 존재해야 하기 때문에 Not Null 제약을 추가로 가지고 있습니다. 외래키로 연결된 게시글이 삭제되면 photos도 같이 삭제됩니다. 

comments
제약 조건	속성	타입
PK	comment_id	BIGINT
NOT NULL	content	TEXT
	display_name	VARCHAR(255)
	password	VARCHAR(255)
DEFAULT CURRENT_TIMESTAMP	created_at	TIMESTAMP
DEFAULT CURRENT_TIMESTAMP	updated_at	TIMESTAMP
DEFAULT FALSE	is_deleted	BOOLEAN
	deleted_at	TIMESTAMP
FK	user_id	BIGINT
FK, NOT NULL, ON DELETE CASCADE	post_id	BIGINT
FK	parent_comment_id	BIGINT

comments 테이블의 구성입니다. 게시글과 동일하게 text 타입의 내용과 비회원을 위한 속성들을 가지고 있습니다. 외래키로는 세 가지 속성을 가지고 있는데 댓글을 작성한 회원의 아이디와 댓글이 작성된 게시글 그리고 대댓글을 위한 부모 댓글의 아이디를 가지고 있습니다. photos와 동일하게 게시글과의 약한 개체 관계로 게시글이 존재해야만 댓글이 존재할 수 있기 때문에 외래키로 연결된 게시글이 삭제된다면 해당 댓글도 함께 삭제됩니다. 댓글에는 댓글이 지워졌는지를 나타내는 is_deleted 속성과 지워진 시점을 기록하는 deleted_at 속성이 존재합니다. 대댓글을 위해 존재하는 속성으로 부모의 댓글이 삭제된다면 대댓글을 같이 삭제하는 것이 아니라 부모 댓글만 삭제되었다는 대체 문구를 출력하고 대댓글의 경우 그대로 존재하게 됩니다. 대신 삭제된 해당 댓글에는 더 이상 대댓글을 달 순 없습니다. 

2.3 SQL - DDL

MySQL 문법에 맞춰 작성된 create문입니다. 

-- 회원 정보 테이블
CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 회원 ID (기본키)
    login_id VARCHAR(255) UNIQUE NOT NULL, -- 회원 아이디
    password VARCHAR(255) NOT NULL, -- 비밀번호 해시
    username VARCHAR(255), -- 회원 이름
    email VARCHAR(255) UNIQUE, -- 이메일 주소 (선택 사항)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 가입 일자
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 수정 일자
    role ENUM('USER', 'MANAGER', 'ADMIN') DEFAULT 'USER' -- 회원 권한 (일반 회원, 매니저, 관리자)
);

-- 게시글 정보 테이블
CREATE TABLE posts (
    post_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 게시글 ID (기본키)
    title VARCHAR(255) NOT NULL, -- 게시글 제목
    content TEXT NOT NULL, -- 게시글 내용
    display_name VARCHAR(255), -- 비회원 이름 (회원일 경우 회원 이름)
    password VARCHAR(255), -- 비회원 비밀번호 해시 (비회원일 경우)
    ip_address VARCHAR(45), -- 비회원 작성자의 IP 주소 (IPv4 및 IPv6 지원)
    recom_count INT DEFAULT 0, -- 추천 수
    not_recom_count INT DEFAULT 0, -- 비추천 수
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 작성 일자
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 수정 일자
    post_type ENUM('NORMAL', 'NOTICE', 'POPULAR') DEFAULT 'NORMAL', -- 게시글 타입
    user_id BIGINT, 회원이 작성한 경우 회원 아이디
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 이미지 정보 테이블
CREATE TABLE photos (
    photo_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 사진 ID (기본키)
    original_photo_name VARCHAR(255) NOT NULL, -- 원본 사진 이름
    saved_photo_name VARCHAR(255) NOT NULL, -- 저장된 사진 이름
    photo_path VARCHAR(255) NOT NULL, -- 사진 경로
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 업로드 일자
    post_id BIGINT NOT NULL, -- 사진이 첨부된 게시글의 ID
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE -- 게시글 삭제 시 사진도 삭제
);

-- 댓글 정보 테이블
CREATE TABLE comments (
    comment_id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 댓글 ID (기본키)
    content TEXT NOT NULL, -- 댓글 내용
    display_name VARCHAR(255), -- 비회원 이름 (회원일 경우 회원 이름)
    password VARCHAR(255), -- 비회원 비밀번호 해시 (비회원일 경우)
    ip_address VARCHAR(45), -- 비회원 작성자의 IP 주소 (IPv4 및 IPv6 지원)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 작성 일자
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 수정 일자
    is_deleted BOOLEAN DEFAULT FALSE, -- 삭제 여부
    deleted_at TIMESTAMP, -- 삭제된 일자
    user_id BIGINT, -- 회원이 작성한 경우 회원 아이디
    post_id BIGINT NOT NULL, -- 댓글이 달린 게시글의 ID
    parent_comment_id BIGINT, -- 부모 댓글 ID (대댓글의 경우 부모 댓글을 참조, 일반 댓글의 경우 NULL)
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE, -- 게시글 삭제 시 댓글도 삭제
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id)
);