[DATABASE - TABLE] 기본테이블생성

** SP_SET_TABLE_COMMENT PROCEDURE 먼저 생성해야 한다. [바로가기]

 

1. TBL_MEMBER

2. TBL_CATEGORY

3. TBL_CONTENTS

4. TBL_CONTENTS_HISTORY

 

/**
author : 정우창
e-mail : ultra102@nate.com
created date : 2014-11-04
projectName : CKMC
description : 회원테이블
**/
create table TBL_MEMBER
(
 PK_memberNumber numeric(10) primary key identity(1000000000, 1) not null,
 memberID varchar(50) unique not null,
 memberPW varchar(44) not null,
 memberRegistDate datetime default getdate() not null,
 memberRegistIP varchar(15) default '0.0.0.0',
 memberGrade tinyint default 1 not null
)
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', '', '회원테이블'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'PK_memberNumber', '회원고유번호(1000000000) - 1씩 자동생성'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'memberID', '회원아이디(메일주소)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'memberPW', '로그인비밀번호(단방향 암호화처리)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'memberRegistDate', '가입신청일'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'memberRegistIP', '가입신청IP'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_MEMBER', 'memberGrade', '회원구분(0:탈퇴,1:가입신청,2:메일인증(일반회원),7:제작자, 8:편집자, 9:마스터관리자'
go

/**
author : 정우창
e-mail : ultra102@nate.com
created date : 2014-11-04
projectName : CKMC
description : 카테고리테이블
**/
CREATE TABLE TBL_CATEGORY
(
 PK_categoryIdx int primary key identity(0,1) not null,
 categoryName nvarchar(10) not null,
 categoryDate datetime default getdate()
)
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CATEGORY', '', '카테고리'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CATEGORY', 'PK_categoryIdx', '카테고리 고유번호(자동생성, 1씩 증가)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CATEGORY', 'categoryName', '카테고리 이름'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CATEGORY', 'categoryDate', '카테고리 생성일시'
go

 

/**
author : 정우창
e-mail : ultra102@nate.com
created date : 2014-11-04
projectName : CKMC
description : 일반게시물 테이블
**/
CREATE TABLE TBL_CONTENTS
(
 PK_contentsIdx BIGINT PRIMARY KEY IDENTITY(1,1) NOT NULL,
 contentsNotiState BIT DEFAULT(0) NOT NULL,
 contentsTitle NVARCHAR(100) NOT NULL,
 contentsText NVARCHAR(MAX) NOT NULL,
 contentsThumbnail VARCHAR(50),
 contentsViewState BIT DEFAULT(0) NOT NULL,
 contentsOpenState BIT DEFAULT(0) NOT NULL,
 contentsPostDate DATETIME DEFAULT GETDATE() NOT NULL,
 contentsOpenDate DATETIME DEFAULT GETDATE() NOT NULL,
 contentsViewCount INT DEFAULT(0) NOT NULL,
 FK_PostMember_MEMBER NUMERIC(10) FOREIGN KEY REFERENCES TBL_MEMBER(PK_memberNumber) NOT NULL,
 FK_OpenMember_MEMBER NUMERIC(10) FOREIGN KEY REFERENCES TBL_MEMBER(PK_memberNumber) DEFAULT('1000000000') NOT NULL,
 FK_CategoryNumber_CATEGORY INT FOREIGN KEY REFERENCES TBL_CATEGORY(PK_categoryIdx) DEFAULT(0) NOT NULL
)
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', '', '일반 게시물 테이블'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'PK_contentsIdx', '게시물 고유번호 - 자동증가'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsNotiState', '게시물공지(0 : 공지하지 않음, 1 : 공지)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsTitle', '게시물제목'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsText', '게시물내용'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsThumbnail', '게시물 썸네일'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsViewState', '게시물 보기 상태(0:등록, 1:보임) - 1로 변경된 이후에다시 0으로 변경불가능'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsOpenState', '게시물 공개 상태(0:잠금, 1:공개)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsPostDate', '게시물작성일시 - 작성일'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsOpenDate', '게시물공개일시 - 발행일'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'contentsViewCount', '게시물조회수'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'FK_PostMember_MEMBER', '작성자 고유번호'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'FK_OpenMember_MEMBER', '발행자 고유번호'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS', 'FK_CategoryNumber_CATEGORY', '카테고리 고유번호'
go

 

/**
author : 정우창
e-mail : ultra102@nate.com
created date : 2014-11-04
projectName : CKMC
description : 일반게시물 등록 및 수정내역 테이블
**/
CREATE TABLE TBL_CONTENTS_HISTORY
(
 PK_contentsHistoryIdx INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
 FK_contentsIdx_CONTENTS BIGINT FOREIGN KEY REFERENCES TBL_CONTENTS(PK_contentsIdx) ON DELETE CASCADE NOT NULL,
 contentsHistoryDate DATETIME DEFAULT GETDATE() NOT NULL,
 contentsHistoryType CHAR(1) DEFAULT('I') NOT NULL,
 contentsHistoryMember NVARCHAR(50) NOT NULL,
 contentsHistoryText NVARCHAR(200) NOT NULL
)
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', '', '일반게시물 등록 및 수정내역 테이블'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', 'FK_contentsIdx_CONTENTS', '일반게시물 고유번호'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', 'contentsHistoryDate', '등록일'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', 'contentsHistoryType', '변경사항-I:등록, U:변경'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', 'contentsHistoryMember', '변경자정보(회원번호-이름)'
go
SP_SET_TABLE_COMMENT 'I', 'TBL_CONTENTS_HISTORY', 'contentsHistoryText', '변경내용(ex : 글상태변경)'
go