[Spring Boot] 3. 테이블 설계 (Week 1)

스프링부트 프로젝트는 오라클 Database를 이용하여 프로젝트를 만들고자 함

 

데이터베이스 접속 툴(tool)은

앞서 프로젝트와 동일하게 dbeaver를 이용 (무료라서 사용하기 편함) 

(https://dbeaver.io/download/ 경로에서 다운로드 및 설치 가능)

Download | DBeaver Community

 

 

Database : XE

 

 

먼저 신규 사용자 (스키마)를 등록한다.

해당 프로젝트에서는 INFO 라는 스키마 (사용자) 를 등록하고 테이블을 생성할 예정 

 

* 테이블 생성 시 아래와 같이 오류 메시지가 출력되면 명령어 실행하여 권한을 부여 

ORA-01950: no privileges on tablespace 'SYSTEM'

alter user INFO quota 50M on SYSTEM;

---

[ 테이블 설계 ]

순번	테이블명	비고
1	사용자정보 테이블	일반사용자, 관리자 권한을 구분할 컬럼이 반드시 필요
2	공통 코드 테이블	게시글 카테고리 관리용 코드 정보 사용자 권한 정보 관리용 코드 정보 사용자 부서 정보 관리용 코드 정보  - 마스터 / 디테일 테이블 구조로 설계
3	게시글 테이블	관리자가 등록한 공지 게시글의 정보를 담는 테이블
4	첨부파일 테이블	게시글에 등록한 첨부파일 정보를 관리하기 위한 테이블
5	커뮤니티 테이블	동호회 등의 커뮤니티 게시판용 테이블
6	댓글 테이블	커뮤니티 게시판 댓글용 테이블
7	설문조사 테이블	설문조사용 마스터/디테일 테이블

 

[ 테이블 생성 ]

- 사용자 정보 테이블

CREATE TABLE INFO.CRT_USER_INFO (
	USER_ID VARCHAR2(20) NOT NULL,
	USER_PW VARCHAR2(100),
	USER_NM VARCHAR2(20),
	USER_AUTH VARCHAR2(10),
	USER_DEPT VARCHAR2(2),
	USE_YN CHAR(1),
	INPUT_DATETIME DATE,
	CONSTRAINT USER_PK PRIMARY KEY (USER_ID)
);
COMMENT ON TABLE INFO.CRT_USER_INFO IS '로그인 사용자 정보 테이블';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USER_ID IS '로그인사용자 아이디';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USER_PW IS '비밀번호';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USER_NM IS '사용자명';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USER_AUTH IS '사용자권한';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USER_DEPT IS '부서코드';
COMMENT ON COLUMN INFO.CRT_USER_INFO.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_USER_INFO.INPUT_DATETIME IS '등록일자';

키 설정을 빼먹은 경우 
ALTER TABLE CRT_USER_INFO ADD CONSTRAINT USER_PK PRIMARY KEY (USER_ID);

 

- 코드정보 테이블 (마스터)

CREATE TABLE INFO.CRT_CODE_MASTER (
	CODE_ID VARCHAR2(20) NOT NULL,
	CODE_NAME VARCHAR2(100),
	INPUT_USER_ID VARCHAR2(50),
	USE_YN CHAR(1),
	INPUT_DATETIME DATE,
	CONSTRAINT CODE_M_PK PRIMARY KEY (CODE_ID)
);
COMMENT ON TABLE INFO.CRT_CODE_MASTER IS '코드 정보 테이블 (마스터테이블)';
COMMENT ON COLUMN INFO.CRT_CODE_MASTER.CODE_ID IS '코드 아이디';
COMMENT ON COLUMN INFO.CRT_CODE_MASTER.CODE_NAME IS '코드명';
COMMENT ON COLUMN INFO.CRT_CODE_MASTER.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_CODE_MASTER.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_CODE_MASTER.INPUT_DATETIME IS '등록일자';

 

- 코드정보 테이블 (디테일)

CREATE TABLE INFO.CRT_CODE_DETAIL (
	CODE_ID VARCHAR2(20) NOT NULL,
	DETAIL_ID VARCHAR(20) NOT NULL,
	CODE_NAME VARCHAR2(100),
	SORT_IDX NUMBER,
	INPUT_USER_ID VARCHAR2(50),
	USE_YN CHAR(1),
	INPUT_DATETIME DATE,
	CONSTRAINT CODE_D_PK PRIMARY KEY (CODE_ID, DETAIL_ID)
);
COMMENT ON TABLE INFO.CRT_CODE_DETAIL IS '코드 정보 테이블 (디테일테이블)';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.CODE_ID IS '코드 아이디';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.DETAIL_ID IS '디테일 코드 아이디';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.CODE_NAME IS '코드명';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.SORT_IDX IS '정렬순서';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_CODE_DETAIL.INPUT_DATETIME IS '등록일자';

 

- 게시글 테이블

CREATE TABLE INFO.CRT_BOARD_INFO (
	BOARD_IDX NUMBER NOT NULL,
	BOARD_TYPE VARCHAR2(20) NOT NULL,
	BOARD_TITLE VARCHAR2(50) NOT NULL,
	BOARD_DESC LONG,
	USE_YN CHAR(1),
	DUE_DATE DATE,
	FIXED_YN CHAR(1),
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	MODIFY_USER_ID VARCHAR2(50),
	MODIFY_DATETIME DATE,
	CONSTRAINT BOARD_INFO_PK PRIMARY KEY (BOARD_IDX)
);
COMMENT ON TABLE INFO.CRT_BOARD_INFO IS '게시판 테이블';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.BOARD_IDX IS '인덱스';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.BOARD_TYPE IS '카테고리';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.BOARD_TITLE IS '게시글 제목';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.BOARD_DESC IS '게시내용';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.DUE_DATE IS '노출마감일자';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.FIXED_YN IS '상단고정유무';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.INPUT_DATETIME IS '등록일자';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.MODIFY_USER_ID IS '수정자';
COMMENT ON COLUMN INFO.CRT_BOARD_INFO.MODIFY_DATETIME IS '수정일자';

 

- 첨부파일 테이블 

CREATE TABLE INFO.CRT_BOARD_FILE (
	BOARD_IDX NUMBER NOT NULL,
	FILE_IDX NUMBER NOT NULL,
	FILE_NAME VARCHAR2(200) NOT NULL,
	ORG_FILE_NAME VARCHAR2(200) NOT NULL,
	USE_YN CHAR(1),
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	CONSTRAINT BOARD_FILE_PK PRIMARY KEY (BOARD_IDX, FILE_IDX)
);
COMMENT ON TABLE INFO.CRT_BOARD_FILE IS '첨부파일 테이블';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.BOARD_IDX IS '인덱스';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.FILE_IDX IS '파일 인덱스';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.FILE_NAME IS '파일명';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.ORG_FILE_NAME IS '실제파일명';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_BOARD_FILE.INPUT_DATETIME IS '등록일자';

 

- 커뮤니티 테이블

CREATE TABLE INFO.CRT_COMMUNITY_INFO (
	COMMUNITY_IDX NUMBER NOT NULL,
	COMMUNITY_TYPE VARCHAR(20) NOT NULL,
	COMMUNITY_TITLE VARCHAR2(50) NOT NULL,
	COMMUNITY_DESC LONG,
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	MODIFY_USER_ID VARCHAR2(50),
	MODIFY_DATETIME DATE,
	CONSTRAINT COMMUNITY_INFO_PK PRIMARY KEY (COMMUNITY_IDX)
);
COMMENT ON TABLE INFO.CRT_COMMUNITY_INFO IS '커뮤니티 게시판 테이블';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.COMMUNITY_IDX IS '커뮤니티 인덱스';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.COMMUNITY_TYPE IS '커뮤니티 타입';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.COMMUNITY_TITLE IS '커뮤니티 게시글 제목';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.COMMUNITY_DESC IS '커뮤니티 게시내용';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.INPUT_DATETIME IS '등록일자';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.MODIFY_USER_ID IS '수정자';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_INFO.MODIFY_DATETIME IS '수정일자';

 

- 댓글 테이블

CREATE TABLE INFO.CRT_COMMUNITY_REPLY (
	COMMUNITY_IDX NUMBER NOT NULL,
	REPLY_IDX NUMBER NOT NULL,
	REPLY_DESC VARCHAR2(500) NOT NULL,
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	CONSTRAINT COMMUNITY_REPLY_PK PRIMARY KEY (REPLY_IDX)
);
COMMENT ON TABLE INFO.CRT_COMMUNITY_REPLY IS '커뮤니티 게시판 답변 테이블';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_REPLY.COMMUNITY_IDX IS '커뮤니티 인덱스';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_REPLY.REPLY_IDX IS '댓글 인덱스';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_REPLY.REPLY_DESC IS '댓글 내용';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_REPLY.INPUT_USER_ID IS '입력자';
COMMENT ON COLUMN INFO.CRT_COMMUNITY_REPLY.INPUT_DATETIME IS '등록일자';

 

- 설문조사 설정 마스터 테이블

CREATE TABLE INFO.CRT_RESEARCH_MASTER (
	RESEARCH_IDX NUMBER NOT NULL,
	RESEARCH_TITLE VARCHAR2(100) NOT NULL,
	DUE_DATE DATE,
	USE_YN CHAR(1),
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	CONSTRAINT RESEARCH_INFO_PK PRIMARY KEY (RESEARCH_IDX)
);
COMMENT ON TABLE INFO.CRT_RESEARCH_MASTER IS '설문조사 설정용 테이블';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.RESEARCH_IDX IS '설문조사 인덱스';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.RESEARCH_TITLE IS '설문조사 제목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.DUE_DATE IS '만료일';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.INPUT_USER_ID IS '등록자';
COMMENT ON COLUMN INFO.CRT_RESEARCH_MASTER.INPUT_DATETIME IS '등록일자';

 

- 설문조사 설정 디테일 테이블 

CREATE TABLE INFO.CRT_RESEARCH_DETAIL (
	RESEARCH_IDX NUMBER NOT NULL,
	DETAIL_IDX NUMBER NOT NULL,
	DETAIL_TITLE VARCHAR2(100) NOT NULL,
	SORT_IDX NUMBER NOT NULL,
	RESEARCH_TYPE CHAR(1) NOT NULL,
	RESEARCH_A VARCHAR2(20),
	RESEARCH_B VARCHAR2(20),
	RESEARCH_C VARCHAR2(20),
	RESEARCH_D VARCHAR2(20),
	RESEARCH_E VARCHAR2(20),
	USE_YN CHAR(1),
	INPUT_USER_ID VARCHAR2(50),
	INPUT_DATETIME DATE,
	CONSTRAINT RESEARCH_DETAIL_PK PRIMARY KEY (RESEARCH_IDX, DETAIL_IDX)
);
COMMENT ON TABLE INFO.CRT_RESEARCH_DETAIL IS '설문조사별 항목설정용 테이블';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_IDX IS '설문조사 인덱스';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.DETAIL_IDX IS '설문조사 항목 인덱스';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.SORT_IDX IS '정렬순서';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.DETAIL_TITLE IS '항목 타이틀';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_TYPE IS '객관식/주관식 형태';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_A IS '1번항목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_B IS '2번항목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_C IS '3번항목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_D IS '4번항목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.RESEARCH_E IS '5번항목';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.USE_YN IS '사용여부';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.INPUT_USER_ID IS '등록자';
COMMENT ON COLUMN INFO.CRT_RESEARCH_DETAIL.INPUT_DATETIME IS '등록일자';