Notice
Recent Posts
Recent Comments
Link
컴퓨터는 잘못이 없다..
[Every Book Day]DB설계 본문
Contents
접기
#Table
개인정보를 저장하는 users 테이블
게시글 정보를 저장하는 게시글 테이블
게시글의 하트 정보를 저장하는 하트(heart) 테이블
게시글의 댓글 정보를 저장하는 댓글(cmt) 테이블(wording제외)
#DB설계 시 고려한 점
1. Foreign key와 ON DELETE CASCADE옵션
▶모든 게시글(report, market, episode, wording, file) 테이블과 관련 하트 테이블에 foreign key와 ON DELETE CASCADE 옵션을 줌
▶모든 게시글 테이블의 writer을 ebd_users테이블의 nick을 참조하는 foreign key로 지정함
┖유저가 탈퇴 시 그 유저가 남긴 게시글이 삭제되도록 만드는 효과
▶모든 하트 테이블의 writer을 ebd_users테이블의 nick을 참조하는 foreign key로 지정함
┖유저가 탈퇴 시 그 유저가 클릭한 하트 정보가 사라지는 효과
▶모든 하트 테이블의 target_num은 관련 게시글 테이블의 num을 참조하는 foreign key로 지정함
┖게시글이 삭제되었을 때 그 게시글에 눌린 하트 정보가 사라지는 효과
2. Trigger
▶ ebd_users테이블의 해당 nick 의 row가 삭제되었을 때 모든 댓글 테이블의 deleted여부가 no에서 yes로 수정 되도록 함
┖유저가 탈퇴 시 댓글이 '삭제된 댓글입니다' 로 바뀌는 효과
▶ ebd_users테이블의 해당 nick의 row가 수정되었을 때 모든 게시글 테이블, 하트 테이블, 댓글 테이블의 nick이 수정되도록 함
┖유저가 닉네임을 변경했을 시 모든 테이블의 닉네임 정보가 update되는 효과
3. 그 외..
1,2번과 CHECK 제약조건, DEFAULT제약조건을 통해 데이터 무결성을 지키려 노력하였음
#Table & Sequence
-- 도서리뷰 회원 테이블
CREATE TABLE ebd_users(
id VARCHAR2(100) PRIMARY KEY, --아이디
pwd VARCHAR2(100) NOT NULL, --비밀번호
name VARCHAR2(100), --이름
nick VARCHAR2(100) NOT NULL, --닉네임
gender VARCHAR2(10), --성별
birth_year VARCHAR2(10), --생년
birth_month VARCHAR2(10), --월
birth_day VARCHAR2(10), --일
email VARCHAR2(100), --이메일
phone VARCHAR2(100), --핸드폰
profile VARCHAR2(2000), --프로필 이미지
regdate DATE, --등록일
CONSTRAINT users_nick_uk UNIQUE(nick), -- 닉네임은 유일해야한다.
CONSTRAINT users_gender_ck CHECK(gender IN('남','여')) --남자일 경우 '남자', 여자일 경우 '여자'가 들어간다.
);
-- 독후감 테이블
CREATE TABLE ebd_report(
num NUMBER PRIMARY KEY, --글 번호
booktitle VARCHAR2(2000), --책 제목
title VARCHAR2(2000), --글 제목
writer VARCHAR2(100), --작성자(닉네임) / join 연결고리
author VARCHAR2(2000), --책 저자
link VARCHAR2(2000), -- 구매처 링크
genre VARCHAR2(100), --책 장르
stars VARCHAR2(50), --평점
imgpath VARCHAR2(2000), --이미지
content CLOB, --내용
publicck VARCHAR2(50) DEFAULT 'private', --공개/비공개 여부 / DEFAULT 'private'
viewcnt NUMBER, --조회수
regdate DATE, --등록일
CONSTRAINT r_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);
-- 독후감 테이블 시퀀스
CREATE SEQUENCE ebd_report_seq;
-- 독후감 댓글 테이블
CREATE TABLE ebd_report_cmt(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자
content VARCHAR2(500), --댓글 내용
target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
ref_group NUMBER, -- 원글(거래 글)의 글 번호
cmt_group NUMBER, -- 댓글의 그룹번호
deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
regdate DATE --댓글 작성일
);
-- 독후감 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_report_cmt_seq;
CREATE TABLE ebd_report_heart(
num NUMBER PRIMARY KEY,
target_num NUMBER,
writer VARCHAR2(100),
CONSTRAINT rh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
CONSTRAINT rh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_report(num) ON DELETE CASCADE
);
CREATE SEQUENCE ebd_report_heart_seq;
-- 도서 에피소드 테이블
CREATE TABLE ebd_episode(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자(로그인 된 아이디)
title VARCHAR2(2000), --제목
imgpath VARCHAR2(2000), --이미지
content CLOB, --내용
viewcnt NUMBER, --조회수
regdate DATE, --등록일
CONSTRAINT e_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);
-- 도서 에피소드 테이블 시퀀스
CREATE SEQUENCE ebd_episode_seq;
-- 독후감 댓글 테이블
CREATE TABLE ebd_episode_cmt(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자
content VARCHAR2(500), --댓글 내용
target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
ref_group NUMBER, -- 원글(거래 글)의 글 번호
cmt_group NUMBER, -- 댓글의 그룹번호
deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
regdate DATE --댓글 작성일
);
-- 독후감 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_episode_cmt_seq;
CREATE TABLE ebd_episode_heart(
num NUMBER PRIMARY KEY,
target_num NUMBER,
writer VARCHAR2(100),
CONSTRAINT eh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
CONSTRAINT eh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_episode(num) ON DELETE CASCADE
);
CREATE SEQUENCE ebd_episode_heart_seq;
-- 명언 테이블 (명언 테이블에는 viewcnt가 없다.)
CREATE TABLE ebd_wording(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자(로그인 된 아이디)
title VARCHAR2(2000), --책 제목
content CLOB, -- 내용(명언)
author VARCHAR2(2000), --책의 작가
regdate DATE, --등록일
CONSTRAINT w_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);
-- 명언 테이블 시퀀스
CREATE SEQUENCE ebd_wording_seq;
CREATE TABLE ebd_wording_heart(
num NUMBER PRIMARY KEY,
target_num NUMBER, --글의 번호
writer VARCHAR2(100), --좋아요 누른 사람
CONSTRAINT wh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
CONSTRAINT wh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_wording(num) ON DELETE CASCADE --지우지마세영~
);
CREATE SEQUENCE ebd_wording_heart_seq;
-- 도서 거래 테이블
CREATE TABLE ebd_market(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자(로그인 된 아이디)
title VARCHAR2(2000), --제목
salestype VARCHAR2(50), --판매유형 나눔/교환/판매
salesstatus VARCHAR2(50), --판매상태 판매중/완료
imgpath VARCHAR2(2000), --이미지 파일
content CLOB, --내용
viewcnt NUMBER, --조회수
regdate DATE, --등록일
CONSTRAINT m_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE --지우지마세영~
);
-- 도서 거래 테이블 시퀀스
CREATE SEQUENCE ebd_market_seq;
--도서 거래 하트 테이블
CREATE TABLE ebd_market_heart(
num NUMBER PRIMARY KEY,
target_num NUMBER,
writer VARCHAR2(100),
CONSTRAINT mh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE, --지우지마세영~
CONSTRAINT mh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_market(num) ON DELETE CASCADE
);
--도서 거래 하트 시퀀스
CREATE SEQUENCE ebd_market_heart_seq;
-- 도서 거래 댓글 테이블
CREATE TABLE ebd_market_cmt(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자
content VARCHAR2(500), --댓글 내용
target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
ref_group NUMBER, -- 원글(거래 글)의 글 번호
cmt_group NUMBER, -- 댓글의 그룹번호
deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
regdate DATE --댓글 작성일
);
-- 도서 거래 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_market_cmt_seq;
-- 독후감 양식 파일 업로드 테이블
CREATE TABLE ebd_file(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자(로그인 된 아이디의 닉네임)
title VARCHAR2(2000), --제목
orgfname VARCHAR2(2000), --파일명
savefname VARCHAR2(2000), --파일명
filesize NUMBER,--파일 사이즈
imgpath VARCHAR2(2000), --이미지 파일
content CLOB, --내용
viewcnt NUMBER, --조회수
regdate DATE, --등록일
CONSTRAINT f_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE
);
-- 독후감 양식 파일 업로드 테이블 시퀀스
CREATE SEQUENCE ebd_file_seq;
-- 독후감 양식 파일 업로드 댓글 테이블
CREATE TABLE ebd_file_cmt(
num NUMBER PRIMARY KEY, --글 번호
writer VARCHAR2(100), --작성자
content VARCHAR2(500), --댓글 내용
target_nick VARCHAR2(100), --답댓글 달 대상자 아이디
ref_group NUMBER, -- 원글(거래 글)의 글 번호
cmt_group NUMBER, -- 댓글의 그룹번호
deleted CHAR(3) DEFAULT 'no', --삭제된 댓글인지의 여부
regdate DATE --댓글 작성일
);
-- 독후감 양식 파일 업로드 댓글의 글번호를 얻어낼 시퀀스
CREATE SEQUENCE ebd_file_cmt_seq;
CREATE TABLE ebd_file_heart(
num NUMBER PRIMARY KEY,
target_num NUMBER,
writer VARCHAR2(100),
CONSTRAINT fh_writer_fk FOREIGN KEY(writer) REFERENCES ebd_users(nick) ON DELETE CASCADE,
CONSTRAINT fh_num_fk FOREIGN KEY(target_num) REFERENCES ebd_file(num) ON DELETE CASCADE
);
CREATE SEQUENCE ebd_file_heart_seq;
#Trigger
--user테이블의 nick이 update 되었을 때 발생하는 trigger
CREATE TRIGGER tg_user_update
AFTER UPDATE OF nick ON ebd_users FOR EACH ROW
BEGIN
UPDATE ebd_report
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_report_cmt
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_report_cmt
SET target_nick = :NEW.nick
WHERE target_nick = :OLD.nick;
UPDATE ebd_report_heart
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_wording
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_wording_heart
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_episode
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_episode_cmt
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_episode_cmt
SET target_nick = :NEW.nick
WHERE target_nick = :OLD.nick;
UPDATE ebd_episode_heart
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_file
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_file_cmt
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_file_cmt
SET target_nick = :NEW.nick
WHERE target_nick = :OLD.nick;
UPDATE ebd_file_heart
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_market
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_market_cmt
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
UPDATE ebd_market_cmt
SET target_nick = :NEW.nick
WHERE target_nick = :OLD.nick;
UPDATE ebd_market_heart
SET writer = :NEW.nick
WHERE writer = :OLD.nick;
END;
/
--user테이블의 행이 삭제되었을 때 발생하는 trigger
CREATE TRIGGER tg_user_delete
AFTER DELETE ON ebd_users FOR EACH ROW
BEGIN
UPDATE ebd_report_cmt
SET deleted = 'yes'
WHERE deleted = 'no' AND writer= :OLD.nick;
UPDATE ebd_episode_cmt
SET deleted = 'yes'
WHERE deleted = 'no' AND writer= :OLD.nick;
UPDATE ebd_file_cmt
SET deleted = 'yes'
WHERE deleted = 'no' AND writer= :OLD.nick;
UPDATE ebd_market_cmt
SET deleted = 'yes'
WHERE deleted = 'no' AND writer= :OLD.nick;
END;
/
Comments
