[Spring] 예제 1 연습 - 테이블 설계

설계 규칙

게시글 하나에 여러 개의 댓글이 작성될 수 있다.

한 게시판에 여러 명의 회원이 댓글을 작성할 수 있다.
한 게시판에 각 회원은 여러 개의 댓글을 작성할 수 있다.

하나의 댓글에 여러 개의 좋아요와 싫어요가 찍힐 수 있다.
각 회원은 한 게시글 당 좋아요와 싫어요 중 한 번,
한 댓글 당 좋아요와 싫어요 중 한 번을 누를 수 있다.

 

(테이블은 계속 추가되거나 바뀔 예정...)

 

 

DDL

DROP SEQUENCE pr_user_seq;
CREATE SEQUENCE pr_user_seq;


DROP SEQUENCE pr_board_seq;
CREATE SEQUENCE pr_board_seq;

DROP SEQUENCE pr_comment_seq;
CREATE SEQUENCE pr_comment_seq;

DROP SEQUENCE pr_boardLikes_seq;
CREATE SEQUENCE pr_boardLikes_seq;

DROP SEQUENCE pr_commentLikes_seq;
CREATE SEQUENCE pr_commentLikes_seq;

DROP SEQUENCE pr_likes_category_seq;
CREATE SEQUENCE pr_likes_category_seq;

DROP TABLE pr_user CASCADE CONSTRAINTS;

CREATE TABLE pr_user(
  userid NUMBER PRIMARY KEY,
  user_id VARCHAR2(20) NOT NULL,
  user_pw VARCHAR2(120) NOT NULL,
  nickname VARCHAR2(100) NOT NULL,
  email VARCHAR2(400),
  birth DATE,
  phone VARCHAR2(14),
  reg_date DATE NOT NULL
);  


DROP TABLE pr_board CASCADE CONSTRAINTS;
CREATE TABLE pr_board(
  boardid NUMBER PRIMARY KEY,
  userid NUMBER REFERENCES pr_user(userid),
  title VARCHAR2(400),
  content VARCHAR2(4000),
  readcount NUMBER,
  reg_date Timestamp NOT NULL
);  

DROP TABLE pr_comment CASCADE CONSTRAINTS;
CREATE TABLE pr_comment(
  commentid NUMBER PRIMARY KEY,
  boardid NUMBER REFERENCES pr_board(boardid),
  userid NUMBER REFERENCES pr_user(userid),
  content VARCHAR2(1000) NOT NULL,
  reg_date Timestamp NOT NULL
);  

DROP TABLE pr_likes_category CASCADE CONSTRAINTS;
CREATE TABLE pr_likes_category (
  likeid NUMBER PRIMARY KEY,
  like_name VARCHAR2(20) NOT NULL
);  


DROP TABLE pr_commentLikes CASCADE CONSTRAINTS;
CREATE TABLE pr_commentLikes(
  c_likeid NUMBER PRIMARY KEY,
  commentid NUMBER REFERENCES pr_comment(commentid),
  userid NUMBER REFERENCES pr_user(userid),
  likeid NUMBER REFERENCES pr_likes_category(likeid)
);

DROP TABLE pr_boardLikes CASCADE CONSTRAINTS;
CREATE TABLE pr_boardLikes(
  b_likeid NUMBER PRIMARY KEY,
  boardid NUMBER REFERENCES pr_board(boardid),
  userid NUMBER REFERENCES pr_user(userid),
  likeid NUMBER REFERENCES pr_likes_category(likeid)
 ); 


COMMIT;

INSERT INTO pr_likes_category VALUES(pr_likes_category_seq.nextval, '좋아요');
INSERT INTO pr_likes_category VALUES(pr_likes_category_seq.nextval, '싫어요');
SELECT * FROM  pr_likes_category ;