DB설계

1. 기존 테이블 설계(2208 ver.)

2. 보완하여 재설계(221228 ver.)

---

2-1. 코드

/*
1. member 테이블
	member_auth_gb 값 : 0 (인증 안 됨), 1 (인증 됨)
*/
CREATE TABLE member (
	member_idx INT AUTO_INCREMENT PRIMARY KEY,
	member_id VARCHAR(50) NOT NULL UNIQUE,
	member_passwd VARCHAR(20) NOT NULL,
	member_name VARCHAR(15) NOT NULL,
	member_gender VARCHAR(15) NOT NULL,	
	member_email VARCHAR(50) NOT NULL UNIQUE,
	member_phone VARCHAR(20) NOT NULL,
	member_join_date VARCHAR(20) NOT NULL,
	member_auth_gb VARCHAR(1) NOT NULL
);

----------------------------------------------------------------------------------------------------------

/* 
2. board 테이블 
	board_gb 값 : 0(공지사항 게시판), 1(자유게시판), 2(이미지 게시판)
*/
CREATE TABLE board (
	board_idx INT AUTO_INCREMENT PRIMARY KEY,
	board_gb VARCHAR(10) NOT NULL,
	board_title VARCHAR(200) NOT NULL,
	board_content VARCHAR(5000) NOT NULL,
	board_writer VARCHAR(50) NOT NULL,
	board_readcount INT NOT NULL,
	board_date VARCHAR(20) NOT NULL
);

----------------------------------------------------------------------------------------------------------

/* 3. file 테이블 */
CREATE TABLE file (
	file_idx INT AUTO_INCREMENT PRIMARY KEY,
	board_idx INT,
	file_real VARCHAR(200),
	file_original VARCHAR(200),
	foreign key (board_idx) references board(board_idx)
	ON DELETE CASCADE
);

----------------------------------------------------------------------------------------------------------

/* 4. reply 테이블 */
CREATE TABLE reply (
	reply_idx INT AUTO_INCREMENT PRIMARY KEY,
	board_idx INT,
	member_idx INT,
	reply_content VARCHAR(2000) NOT NULL,
	reply_re_ref INT NOT NULL,
	reply_re_lev INT NOT NULL,
	reply_re_seq INT NOT NULL,
	reply_date VARCHAR(20) NOT NULL,
	foreign key (board_idx) references board(board_idx)
	ON DELETE CASCADE,
	foreign key (member_idx) references member(member_idx)
	ON DELETE SET NULL
);

----------------------------------------------------------------------------------------------------------

/* 5. auth_info 테이블 */
CREATE TABLE auth_info (
	member_idx INT,
	auth_code VARCHAR(100),
	foreign key (member_idx) references member(member_idx)
	ON DELETE CASCADE
);

----------------------------------------------------------------------------------------------------------

/* 6. address 테이블 */
CREATE TABLE address (
	address_idx INT AUTO_INCREMENT PRIMARY KEY,
	member_idx INT,
	address_zipcode VARCHAR(10) NOT NULL,
	address1 VARCHAR(50) NOT NULL,
	address2 VARCHAR(50) NOT NULL,
	foreign key (member_idx) references member(member_idx)
	ON DELETE CASCADE
);

----------------------------------------------------------------------------------------------------------

/* 7. like_list 테이블 */
CREATE TABLE like_list (
	like_idx INT AUTO_INCREMENT PRIMARY KEY,
	member_idx INT,
	board_idx INT,
	foreign key (member_idx) references member(member_idx)
	ON DELETE CASCADE,
	foreign key (board_idx) references board(board_idx)
	ON DELETE CASCADE
);

3. 주안점

• 관계형 데이터베이스로 재설계 (+제약조건 설정)
• 추가 기능을 고려 (좋아요, 메일인증 등)
• 기존에는 게시판 유형마다 테이블을 뒀다면 게시판 테이블 하나와 gb 컬럼을 둬, 보다 효율적인 테이블 사용

4. 수정 사항

• 23.01.11
  - member 테이블의 member_passwd 컬럼 크기를 20바이트에서 200바이트로 변경 (암호문 담아야해서)

  ALTER TABLE `ooo`.`member` 
  CHANGE COLUMN `member_passwd` `member_passwd` VARCHAR(200) NOT NULL ;

• 23.01.18
  - auth_info 테이블의 member_idx 컬럼에 UNIQUE 제약조건 추가 (ON DUPLICATE KEY UPDATE 사용을 위해)

  ALTER TABLE `ooo`.`auth_info`
  		ADD UNIQUE INDEX `member_idx_UNIQUE` (`member_idx` ASC) VISIBLE;