PostgreSQL 10 | 예제

무결성을 유지하며 테이블 만들기

요구사항 분석

1. 게시판은 다음 요소를 포함하고 있다.
   1.0. PK 값
   1.1. 게시판 제목
   1.2. 작성자
   1.3. 작성일자
   1.4. 주제코드
   1.5. 제목
   1.6. 내용
   1.7. 공개 / 비공개 여부
   1.8. 좋아요
   1.9. 댓글 목록

2. 댓글은 다음의 요소를 포함하고 있다.
   2.0. PK 값
   2.1. 작성자
   2.2. 작성일자
   2.3. 내용
   2.4. 공개 / 비공개 여부

3. 사용자 정보는 다음의 요소를 포함하고 있다.
   3.0. PK 값
   3.1. 아이디
   3.2. 패스워드
   3.3. 나이
   3.4. 가입일자
   3.5. 국가코드

4. 국가 정보는 다음의 요소를 포함하고 있다.
   4.0. PK 값
   4.1. 국가명
   4.2. 대륙

ERM 도식화

상황 설명

제약 조건

본 서비스는 성인용 컨텐츠라는 전제를 하고
users.age 에 20 보다 크거나 같아야 한다라는 제약 조건을 걸었다.

빈 게시글과 댓글을 막기 위하여
post.post_text 와 post_comment.comment_text에 길이 제한을 걸었다.

user 가 탈퇴하면 그 사람이 작성한 글과 댓글을 삭제하기 위해
users 를 참조하고 있는 post 와 post_comment 에 ON DELETE CASCADE 를 추가했다.

사이트에서 지원되는 topic 이 제거되면
비즈니스 로직 상 접근이 불가능하게 만들어야 한다라는 전제를 했고
불필요한 데이터를 삭제하기 위하여 post 의 post_topic 칼럼에 ON DELETE CASCADE 를 추가하였다.

양방향 지향

ERM 에는 혼란을 피하기 위하여 별도로 참조 표시를 하지 않았으나,
쿼리문을 확인해보면 아래의 요소들을 볼 수 있다.

users.user_post INTEGER[]
users.user_comment INTEGER[]
post.comment_list INTEGER[]

구조적 지향

현재 커뮤니티 서비스는
많은 접속 요청과 많은 기능들을 기본적으로 포함하고 있는 추세이다.
특히, 특정 유저가 쓴 게시글이나 댓글 보기 등의 다양한 기능은 매우 편리하다.
이러한 경우 일일히 HWERE 조건 문으로 댓글 리스트에서 파일을 찾는 것은 매우 비효율적인 구조라고 생각했다. 따라서 별도로 array 형태 혹은 json 형태로 pk 값을 저장해두는 방식을 선택했다.

쿼리문

CREATE TABLE country (
    country_id SERIAL NOT NULL PRIMARY KEY,
    country_name VARCHAR(300) NOT NULL,
    country_region VARCHAR(300) NOT NULL
);

CREATE TABLE post_topic (
    topic_id SERIAL NOT NULL PRIMARY KEY,
    topic_name VARCHAR(300) NOT NULL
);

CREATE TABLE users (
    user_id SERIAL NOT NULL PRIMARY KEY,
    user_email VARCHAR(300) NOT NULL,
    user_password VARCHAR(300) NOT NULL,
    user_age INTEGER NOT NULL CHECK ( user_age >= 20 ),
    user_created DATE NOT NULL,
    user_country INTEGER REFERENCES country ON DELETE SET DEFAULT,
    user_post INTEGER[],
    user_comment INTEGER[]
);

CREATE TABLE post (
    post_id SERIAL NOT NULL PRIMARY KEY,
    post_owner INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    post_craeted DATE NOT NULL,
    post_topic INTEGER NOT NULL REFERENCES post_topic ON DELETE CASCADE,
    post_title VARCHAR(300) NOT NULL,
    post_text TEXT NOT NULL CHECK ( length(post_text) > 100 ),
    public_option BOOLEAN NOT NULL DEFAULT true,
    like_option INTEGER,
    comment_list INTEGER[]
);

CREATE TABLE post_comment (
    comment_id SERIAL NOT NULL PRIMARY KEY,
    comment_owner INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    comment_created DATE NOT NULL,
    comment_text TEXT NOT NULL CHECK ( length(comment_text) > 100 ),
    public_option BOOLEAN NOT NULL DEFAULT true
);