61일차. Team Project DB 생성

오늘은 ERD Cloud의 설계를 마치고 DB 생성에 돌입했다.

 

사용Database : MYSQL 5.7.40

 

먼저 database 생성하고 user 생성후 접근권한 설정

 

create database gosu default character set utf8;

-- 계정 생성 및 권한부여 하나씩 차근차근 실행
create user 'gosu123'@'localhost' identified by 'asdf123';
grant all privileges on gosu.* to 'gosu123'@'localhost';

create user 'gosu123'@'%' identified by 'asdf123';
grant all privileges on gosu.* to 'gosu123'@'%';

 

DB의 이름은 gosu 

User의 이름은 gosu123이다.

 

그리고 해당 권한을 다 부여해줬으면 생성한 계정인 gosu123으로 접속한다.

 

그후 테이블 생성 및 제약조건과 Primary key, 그리고 foreign key 설정후 생성

 

반응형

 

-- user table
CREATE TABLE `USERINFO` (
	`user_no`	int	NOT NULL auto_increment primary key,
	`user_id`	varchar(20)	NOT NULL unique,
	`user_pwd`	varchar(40)	NOT NULL,
	`user_name`	varchar(20)	NOT NULL,
	`user_hp`	varchar(15)	NOT NULL,
	`user_regdate`	datetime	NOT NULL	DEFAULT now(),
	`user_address`	varchar(100)	NOT NULL,
	`user_grade`	int(3)	NOT NULL	DEFAULT 1	COMMENT '유저 1, 관리자999',
	`user_email`	varchar(100)	NOT NULL,
	`user_gender`	varchar(2)	NOT NULL check(user_gender in('M','F')) ,
	`user_skill`	varchar(100) check(`user_skill` in('정보처리산업기사','정보처리기사','OCJP','OCJD')),
	`user_school`	varchar(200) check(`user_school` in('고등학교졸업','전문대졸업','4년대학졸업','학사','박사')),
	`user_birth`	int(10)	NOT NULL
) engine=InnoDB default character set=utf8;

CREATE TABLE `NOTICE_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(50)	NOT NULL,
	`comm_volt`	int	NULL	DEFAULT 0	COMMENT '좋아요수',
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y',
	`notice_no`	int,
	foreign key(`notice_no`) references NOTICEBOARD(notice_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `NOTICEBOARD` (
	`notice_no`	int	NOT NULL auto_increment primary key,
	`user_name`	varchar(12)	NOT NULL,
	`notice_title`	varchar(100)	NOT NULL,
	`notice_content`	varchar(2000)	NOT NULL,
	`notice_credate`	datetime	NOT NULL	DEFAULT now(),
	`notice_update`	datetime	NULL	COMMENT '수정할때 now()처리',
	`notice_readcnt`	int	not NULL	DEFAULT 0,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y',
    `user_no` int,
	foreign key (`user_no`) references userinfo(user_no) 
) engine=InnoDB default character set=utf8;



CREATE TABLE `TRADEBOARD` (
	`trade_no`	int	NOT NULL auto_increment primary key,
	`user_name`	varchar(12)	NOT NULL,
	`trade_title`	varchar(255)	not NULL,
	`trade_content`	varchar(3000)	not NULL,
	`trade_credate`	datetime	NULL	DEFAULT now(),
	`trade_update`	datetime	NULL,
	`trade_readcnt`	int	not NULL	DEFAULT 0,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y',
	`user_no`	int	NOT NULL,
    foreign key (`user_no`) references userinfo(user_no)
) engine=InnoDB default character set=utf8;



CREATE TABLE `HELPBOARD` (
	`help_no`	int	not NULL auto_increment primary key,
	`help_title`	varchar(255)	NOT NULL,
	`help_content`	varchar(3000)	NOT NULL,
	`user_name`	varchar(12)	not NULL,
	`help_credate`	datetime	NULL	DEFAULT now(),
	`help_update`	datetime	NULL,
	`help_readcnt`	int	not NULL	DEFAULT 0,
	`isshow`	varchar(2)	NULL	DEFAULT 'Y',
	`user_no`	int	NOT NULL,
	`help_category`	varchar(10)	NULL check(help_category('java','javascript','c++','database','python')),
    foreign key (`user_no`) references userinfo(user_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `REVIEWBOARD` (
	`review_no`	int	NOT NULL auto_increment primary key,
	`user_name`	varchar(20)	not NULL,
	`review_title`	varchar(255)	not NULL,
	`review_content`	varchar(3000)	not NULL,
	`review_credate`	datetime	not NULL default now(),
	`review_update`	datetime	NULL,
	`review_readcnt`	int	not NULL	DEFAULT 0,
	`isshow`	varchar(2)	NULL	DEFAULT 'Y',
	`review_category`	varchar(20)	NULL check(review_category in('리뷰','후기')),
	`user_no`	int	NOT NULL,
    foreign key (`user_no`) references userinfo(user_no)
) engine=InnoDB default character set=utf8;


CREATE TABLE `HELPERBOARD` (
	`helper_no`	int	NOT NULL auto_increment primary key,
	`user_name`	varchar(20) not	NULL,
	`helper_title`	varchar(255)	NOT NULL,
	`helper_content`	varchar(3000)	NOT NULL,
	`helper_credate`	datetime	NOT NULL	DEFAULT now(),
	`helper_update`	datetime	NOT NULL,
	`helper_readcnt`	int	not NULL	DEFAULT 0,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y',
	`user_no`	int	NOT NULL,
	`helper_category`	varchar(10)	NULL check(help_category('java','javascript','c++','database','python')),
    foreign key (`user_no`) references userinfo(user_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `FREEBOARD` (
	`free_no`	int	NOT NULL auto_increment primary key,
	`free_title`	varchar(100)	not NULL,
	`free_content`	varchar(1500)	not NULL,
	`free_credate`	datetime	not NULL	DEFAULT now(),
	`free_update`	datetime	NULL,
	`free_readcnt`	int	not NULL	DEFAULT 0,
	`user_name`	varchar(12)	NOT NULL,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y',
	`free_category`	varchar(20)	NULL check(free_category in('자유','질문','TIP')),
	`user_no`	int	NOT NULL,
    foreign key (`user_no`) references userinfo(user_no)
) engine=InnoDB default character set=utf8;


CREATE TABLE `HELP_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(50)	NOT NULL,
	`comm_conn`	varchar(2)	NULL	COMMENT '채택되면 Y',
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y'	COMMENT '노출Y
비노출 N',
	`help_no`	int	NULL,
    foreign key (`help_no`) references helpboard(help_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `HELPER_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(20)	NOT NULL,
	`comm_conn`	varchar(2)	NULL	COMMENT '채택되면 Y',
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y'	COMMENT '노출Y
비노출 N',
	`helper_no`	int	NOT NULL,
    foreign key (`helper_no`) references helperboard(helper_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `TRADE_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(20)	NOT NULL,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y'	COMMENT '노출Y
비노출 N 기본값 Y',
	`trade_no`	int	NOT NULL,
	`comm_volt`	int	not NULL	DEFAULT 0	COMMENT '좋아요수',
    foreign key (`trade_no`) references tradeboard(trade_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `FREE_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(30)	NOT NULL,
	`isshow`	varchar(2)	NULL	DEFAULT 'Y'	COMMENT '노출Y
비노출 N',
	`comm_volt`	int	not NULL	DEFAULT 0	COMMENT '좋아요수',
	`free_no`	int	NOT NULL,
    foreign key (`free_no`) references freeboard(free_no)
) engine=InnoDB default character set=utf8;

CREATE TABLE `REVIEW_COMMENT` (
	`comm_no`	int	NOT NULL auto_increment primary key,
	`comm_content`	varchar(1000)	not NULL,
	`comm_credate`	datetime	not NULL	DEFAULT now(),
	`user_name`	varchar(30)	NOT NULL,
	`isshow`	varchar(2)	not NULL	DEFAULT 'Y'	COMMENT '노출Y
비노출 N',
	`comm_volt`	int	not NULL	DEFAULT 0	COMMENT '좋아요수',
	`review_no`	int	NOT NULL,
    foreign key (`review_no`) references reviewboard(review_no)
) engine=InnoDB default character set=utf8;



-- dummy user data 삽입

insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('faker','1234','이상혁','01088885555','서울시 강남구','faker@t1.com','M','880808');

insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('keria','1234','류민석','01088885555','서울시 강남구','keria@t1.com','M','880101');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('hongid','1234','홍홍홍','01088885555','서울시 강남구','hongid@naver.com','F','880102');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('kin','1234','조두팔','01088885555','서울시 강남구','kin@google.com','F','880801');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('pyosik','1234','홍창현','01088885555','서울시 강남구','pyosik@gmail.com','M','810808');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('smith','1234','김미스','01088885555','서울시 강남구','smith@nate.com','F','180808');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('bravo','1234','황정민','01088885555','서울시 강남구','bravo@buddy.com','M','820808');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('jungjae','1234','이정재','01088885555','서울시 강남구','lee@hanmail.net','M','890108');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('kkoodk','1234','아무개','01088885555','서울시 강남구','dkanro@naver.com','F','280808');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('choi','1234','홍초이','01088885555','서울시 강남구','choi@choi.com','F','830808');
insert into userinfo(user_id, user_pwd, user_name, user_hp, user_address, user_email, user_gender, user_birth)
values('deft','1234','알파카','01088885555','서울시 강남구','deft@t2.com','M','830808');

-- 더미데이터 확인
select * from userinfo;

-- 커밋
commit;

 

 

총 6개의 게시판 Table . 그리고 각각의 게시판 번호를 외래키로 받아온 6개의 댓글 게시판 Table

 

카테고리는 Table을 따로 생성할까 했지만 강사님의 의견을 듣고 컬럼으로 합쳤다.

 

그리고 Dummy 회원 데이터를 몇개 추가해주고 commit

 

이제 DB 생성을 마쳤으니 내일부터는 코드다 코드!

 

 

각 관계설정은 전부 공통으로 비식별 관계로 설정하였다. 외래키와 기본키로 참조키를 가지다 보면

 

식별관계에서는 자식이 있는경우 부모의 데이터를 삭제를 할수가 없다. Mysql에서는 이를 무시하는 명령어를 별도로 지정해줄수 있지만.

 

여러 사이트를 벤치마킹 해본결과, 회원의 글에 댓글이 달려있다고해서 글이 삭제가 되지않는 경우는 없었다.

 

10개중 10개는 회원의 글이 삭제되면 그 글에 달린 댓글도 같이 삭제되는경우가 다였다.

 

회원도 마찬가지 이다. 회원탈퇴를 하기위해서 게시글을 다 지워야하는게 아니다. 게시글이 있건 없건 탈퇴가 되야한다.

 

그래서 전부 비식별 관계로 처리해주었다.