[JSP] 공부 인증 게시판 웹 프로젝트 : 데이터베이스 설계, DDL
JSP를 공부한 후 공부 인증을 주제로하는 웹 프로젝트를 진행하게 되었다.
이번 프로젝트를 진행하면서 나의 고찰들을 포스팅 하려고 한다.
1. 회원 테이블
-- 회원
create table members(
member_idx int AUTO_INCREMENT not null comment '회원 index',
member_id varchar(30) unique not null comment '회원 id',
member_nickname varchar(30) unique not null comment '회원 닉네임',
member_regdate date DEFAULT now() comment '회원가입 날짜',
member_email varchar(50) not null comment '회원 이메일',
member_image_path varchar(255) null comment '회원 프로필 사진',
member_zonecode varchar(30) not null comment '우편번호',
member_address text not null comment '도로명 주소',
member_address_detailed text not null comment '상세 주소',
PRIMARY KEY (member_idx)
);
-- 회원 비밀번호
create table password_members(
password_member_idx int not null AUTO_INCREMENT COMMENT '회원 비밀번호 index',
member_idx int not null COMMENT '회원 index',
password_member varchar(128) not null COMMENT '비밀번호',
PRIMARY KEY (password_member_idx),
FOREIGN KEY (member_idx) REFERENCES members (member_idx) on delete cascade on update cascade
);
- 회원 테이블과 회원 비밀번호 테이블을 분리하였다.
여러 레퍼런스를 참고하다가 회원 정보 테이블과 비밀번호 테이블이 분리된 것을 보게 되었다. sql injection에 대응하기 위해 분리하여 설계하였다고 한다. 회원 테이블을 알아낸다 하더라도 회원 테이블과 비밀번호 테이블이 분리되어 있기에 한 번에 알 수 없다는 것이 장점이다.
- 비밀번호 암호화
그리고 회원의 비밀번호도 회원이 직접 입력한 문자열이 아니라 암호화를 하여 저장할 것이다. SHA-256 해싱 기법을 사용하여 암호화할 것이고, salt를 사용할 것이다. 내가 참고한 블로그에선은 salt값을 DB에 저장해주었는데 검색해보니 salt값을 DB에 저장하면 안 된다고 한다. 이 부분은 다시 검색해보아야 할 것 같다.
그리고 인코딩할 때, Java 프로그램 혹은 DB 서버에서 암호화가 가능하다. 나의 생각으로는 DBMS에서 암호화를 하게 될 경우 데이터양이 많을 경우 과부하가 생겨 DB 데이터에 영향이 갈 가능성이 있어 Java 프로그램 내에서 암호화를 수행하는 것이 맞다고 생각한다. 데이터베이스의
또한, 회원 정보 테이블도 수정되지 않는 정보, 자주 수정되는 정보로 나누어서 저장하는 구조도 보았다. 만약 그렇게 나누게 된다면 데이터를 잘못 수정하게 될 가능성이 줄어들게 되어 유지보수가 편해질 것이라는 생각이 들었다. 처음 보는 구조라서 신기했지만 나의 이번 프로젝트에서는 그렇게까지 모듈화하지 않아도 될 것 같아서 적용하지는 않았다.
회원가입 시, 다음 지도 API를 사용하여 주소 정보를 입력받는다. 그래서 우편번호, 도로명 주소, 상세 주소 속성을 넣어 주었다.
2. 관리자 테이블
-- 관리자
CREATE TABLE admins (
admin_idx INT NOT NULL AUTO_INCREMENT COMMENT '관리자 index',
admin_id VARCHAR(12) UNIQUE NOT NULL COMMENT '관리자 id',
PRIMARY KEY (admin_idx)
);
-- 관리자 비밀번호
CREATE TABLE password_admins (
password_admin_idx INT NOT NULL AUTO_INCREMENT COMMENT '관리자 비밀번호 index',
admin_idx INT NOT NULL COMMENT '관리자 index',
password_admin VARCHAR(128) NOT NULL COMMENT '비밀번호',
PRIMARY KEY (password_admin_idx),
FOREIGN KEY (admin_idx) REFERENCES admins(admin_idx) on update CASCADE on delete CASCADE
);
관리자 기능은 기본 기능만 구현할 것이기 때문에 매우 심플하게 설계하였다. 모든 관리자는 모든 게시판에 접근할 수 있다.
3. 게시판 테이블
-- 게시판
create table boards(
board_idx INT NOT NULL AUTO_INCREMENT COMMENT '게시판 index',
board_description varchar(100) COMMENT '게시판 설명',
PRIMARY KEY (board_idx)
);
공부 인증 게시판과 공지 게시판을 만들 것이다. 공부 인증 게시판의 index는 1, 공지 게시판의 index는 2로 설정할 것이다. 게시판이 추가될 수록 이 테이블의 레코드도 증가할 것이다.
4. 공부 인증 게시판 테이블
-- 공부 인증 게시판의 게시물
CREATE TABLE posts (
post_idx INT NOT NULL AUTO_INCREMENT COMMENT '공부인증 게시물 index',
board_idx INT NOT NULL COMMENT '게시판 index',
post_title VARCHAR(100) NOT NULL COMMENT '제목',
post_content TEXT NOT NULL COMMENT '내용',
member_idx INT NOT NULL COMMENT '작성자 회원 index',
post_likecount INT DEFAULT 0 COMMENT '좋아요 수',
post_write_date DATE DEFAULT NOW() COMMENT '작성일시',
post_update_date DATE NULL COMMENT '수정일시',
post_visitcount INT DEFAULT 0 COMMENT '조회수',
PRIMARY KEY (post_idx),
FOREIGN KEY (board_idx) REFERENCES boards(board_idx) on update CASCADE on delete CASCADE,
FOREIGN KEY (member_idx) REFERENCES members(member_idx) on update CASCADE on delete CASCADE
);
-- 공부 인증 게시판 게시물에 첨부되는 이미지
create table post_images(
post_image_idx int NOT NULL AUTO_INCREMENT comment '이미지 index',
post_idx int not null comment '게시물 index',
post_image_path text not null comment '이미지 저장 경로',
post_t_image_path text comment '썸네일 이미지 저장 경로',
PRIMARY KEY (post_image_idx),
FOREIGN KEY (post_idx) REFERENCES posts(post_idx) on delete cascade on update cascade
);
공부 인증 게시판에 올라오는 게시물은 게시판 index = 1을 외래키로 가지고 있다.
그리고 웹 사이트의 트래픽 과부하를 방지하기 위해 썸네일을 따로 저장해줄 것이므로 썸네일 이미지 저장 경로 속성을 추가하였다.
5. 공지 게시판 테이블
-- 공지 게시판의 게시물
Create TABLE notices (
notice_idx INT NOT NULL AUTO_INCREMENT COMMENT '공지 게시물 index',
board_idx INT NOT NULL COMMENT '게시판 index',
notice_title VARCHAR(100) NOT NULL COMMENT '제목',
notice_content TEXT NOT NULL COMMENT '내용',
admin_idx INT NOT NULL COMMENT '작성자 관리자 index',
notice_write_date DATE DEFAULT NOW() COMMENT '작성일시',
notice_update_date DATE NULL COMMENT '수정일시',
notice_visitcount INT DEFAULT 0 COMMENT '조회수',
PRIMARY KEY (notice_idx),
FOREIGN KEY (board_idx) REFERENCES boards(board_idx) on update CASCADE on delete CASCADE,
FOREIGN KEY (admin_idx) REFERENCES admins(admin_idx) on update CASCADE on delete CASCADE
);
공부 인증 게시판과 동일하다. 공지 게시판의 작성자 index에는 관리자 index를 외래키로 받는다.
공지 게시물에는 이미지 첨부와 댓글 작성이 불가능하도록 구현할 것이므로 따로 테이블이 없다.
6. 공부 인증 게시물의 댓글 테이블
-- 공부 인증 게시판 게시물의 댓글
CREATE TABLE replies (
reply_idx INT AUTO_INCREMENT COMMENT '댓글 인덱스번호',
post_idx INT NOT NULL COMMENT '게시물 index',
reply_content TEXT NOT NULL COMMENT '댓글 내용',
member_idx INT NOT NULL COMMENT '작성자 index',
reply_write_date DATE DEFAULT now() COMMENT '댓글 작성일시',
reply_update_date DATE NULL COMMENT '댓글 수정일시',
reply_parent_no INT NOT NULL DEFAULT 0 COMMENT '댓글 부모번호(원댓글)',
reply_depth_no INT NOT NULL DEFAULT 0 COMMENT '계층',
reply_orderid INT NOT NULL COMMENT '같은 그룹 정렬',
PRIMARY KEY (reply_idx),
FOREIGN KEY (post_idx) REFERENCES posts(post_idx) on delete cascade on update cascade,
FOREIGN KEY (member_idx) REFERENCES members(member_idx) on delete cascade on update cascade
);
계층형 댓글(댓글 들여쓰기)을 위해 부모번호, 계층, 같은 그룹 정렬 속성을 추가하였다.
7. 공부 인증 게시물 좋아요(추천) 테이블
CREATE TABLE likes(
post_idx INT NOT NULL COMMENT '게시글IDX',
member_idx INT NOT NULL COMMENT '회원IDX',
CONSTRAINT likes_PK PRIMARY KEY (post_idx, member_idx),
FOREIGN KEY (post_idx) REFERENCES posts(post_idx) on delete cascade on update cascade,
FOREIGN KEY (member_idx) REFERENCES members(member_idx) on delete cascade on update cascade
);
게시물 index와 회원 index를 primary key로 사용한다.
Refs