국비 Spring 프로젝트 DB설계 및 화면UI

#7 반려동물 산책코스

 

DB설계

app.diagrams.net/

Flowchart Maker & Online Diagram Software

 

 

dog => 산책코스 리스트, 디테일

CREATE TABLE dog(
    no NUMBER PRIMARY KEY, --글번호
    loc VARCHAR2(45) NOT NULL, --위치
    star NUMBER NOT NULL, --난이도 별3개까지
    time NUMBER NOT NULL, --산책예상시간
    title VARCHAR2(45) NOT NULL, --제목
    poster VARCHAR2(100) NOT NULL, --사진
    writer VARCHAR(45), --작성자
    content CLOB NOT NULL, --내용
    regdate VARCHAR2(45), --작성일
    hit NUMBER DEFAULT 0, --조회수
    like NUMBER DEFAULT 0, --좋아요
   tag1 VARCHAR2(10), --태그1 위치
   tag2 VARCHAR2(10), --태그2 특징
   tag3 VARCHAR2(10), --태그3 특징2
    CONSTRAINT dog_no_pk PRIMARY KEY(no)
);

---

 

 

dogreply => 산책코스 댓글

CREATE TABLE dogreply(
    no NUMBER PRIMARY KEY, --댓글번호
    dno NUMBER NOT NULL, --글번호
    id VARCHAR2(20) CONSTRAINT dog_id_nn NOT NULL, --글쓴이아이디
    msg CLOB CONSTRAINT dog_msg_nn NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0,
    CONSTRAINT dog_rno_pk PRIMARY KEY(no),
    CONSTRAINT dog_dno_fk FOREIGN KEY(dno)
    REFERENCES dog(no)    
);

---

 

dog_board1,2 => 같이가요, 자랑해요

 

CREATE TABLE dog_board1(
    no NUMBER PRIMARY KEY, --게시판글번호
    id VARCHAR2(20) CONSTRAINT dog1_id_nn NOT NULL, --아이디
    subject VARCHAR2(100) CONSTRAINT dog1_sub_nn NOT NULL, --제목
    content CLOB CONSTRAINT dog1_cont_nn NOT NULL, --내용
    pwd VARCHAR2(20) CONSTRAINT dog1_pwd_nn NOT NULL, --비밀번호
    regdate DATE DEFAULT SYSDATE, --작성일시
    hit NUMBER DEFAULT 0, --조회수
    poster VARCHAR2(100), --이미지
    filename VARCHAR2(1000),
    filesize VARCHAR2(1000),
    filecount NUMBER DEFAULT 0,
    CONSTRAINT dogb1_no_pk PRIMARY KEY(no)
);
CREATE TABLE dog_board2(
    no NUMBER PRIMARY KEY, --게시판글번호
    id VARCHAR2(20) CONSTRAINT dog2_id_nn NOT NULL, --아이디
    subject VARCHAR2(100) CONSTRAINT dog2_sub_nn NOT NULL, --제목
    content CLOB CONSTRAINT dog2_cont_nn NOT NULL, --내용
    pwd VARCHAR2(20) CONSTRAINT dog2_pwd_nn NOT NULL, --비밀번호
    regdate DATE DEFAULT SYSDATE, --작성일시
    hit NUMBER DEFAULT 0, --조회수
    poster VARCHAR2(100), --이미지
    filename VARCHAR2(1000),
    filesize VARCHAR2(1000),
    filecount NUMBER DEFAULT 0,
    CONSTRAINT dogb2_no_pk PRIMARY KEY(no)
);​

---

 

dog_board_reply1,2 => 게시판1,2 댓글

CREATE TABLE dog_board_reply1(
    no NUMBER PRIMARY KEY, --댓글번호
    bno NUMBER NOT NULL, --글번호
    id VARCHAR2(20) CONSTRAINT dogb1_id_nn NOT NULL, --글쓴이아이디
    msg CLOB CONSTRAINT dogb1_msg_nn NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0,
    CONSTRAINT dogb1_rno_pk PRIMARY KEY(no),
    CONSTRAINT dogb1_bno_fk FOREIGN KEY(bno)
    REFERENCES dog_board1(no)
);
CREATE TABLE dog_board_reply2(
    no NUMBER PRIMARY KEY, --댓글번호
    bno NUMBER NOT NULL, --글번호
    id VARCHAR2(20) CONSTRAINT dogb2_id_nn NOT NULL, --글쓴이아이디
    msg CLOB CONSTRAINT dogb2_msg_nn NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0,
    CONSTRAINT dogb2_rno_pk PRIMARY KEY(no),
    CONSTRAINT dogb2_bno_fk FOREIGN KEY(bno)
    REFERENCES dog_board2(no)
);

---

 

dog_board3 => 익명게시판

CREATE TABLE dog_board3(
    no NUMBER PRIMARY KEY, --게시판글번호
    name VARCHAR2(20) CONSTRAINT dog3_nm_nn NOT NULL, --닉네임
    pwd VARCHAR2(20) CONSTRAINT dog3_pwd_nn NOT NULL, --비밀번호
    msg CLOB CONSTRAINT dogb3_msg_nn NOT NULL, --내용
    regdate DATE DEFAULT SYSDATE, --작성일시
    ip VARCHAR2(45),
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0,
    CONSTRAINT dogb3_no_pk PRIMARY KEY(no)
);

---

 

dog_plan => 일정만들기

CREATE TABLE dog_plan(
    no NUMBER PRIMARY KEY,
    id VARCHAR2(20) CONSTRAINT dogp_id_nn NOT NULL,
    dno NUMBER CONSTRAINT dogp_dno_nn NOT NULL, --dog테이블 글번호
    loc VARCHAR2(45), --위치 서울시 25개 구
    des VARCHAR2(45), --산책지 destination
    day VARCHAR2(45), --산책일자
    time VARCHAR2(10), --산책시간
    dur NUMBER, --경과시간
    CONSTRAINT dogp_no_pk PRIMARY KEY(no),
    CONSTRAINT dogp_dno_fk FOREIGN KEY(dno)
    REFERENCES dog(no)
);