Life Module DB 설계

[KimYeounHong97]

ERD Design DB - Mysql

[KimYeounHong97]

회원관련 테이블 -회원 (마스터 테이블) -회원 권한

매거진 테이블 -매거진 (마스터 테이블) -매거진 첨부파일 -동물 -예술 & 엔터테인먼트 -목적지 -역사 -생활 -자연 -사람들

메인페이지에는 각 섹터별로 관리자가 지정해놓은 포스트만 보여주기

[KimYeounHong97]

https://www.erdcloud.com/d/H9fSm9rSFg5nwfDSy

[KimYeounHong97]

DROP TABLE IF EXISTS Member;

CREATE TABLE Member ( USER_ID VARCHAR(10) NOT NULL, USER_PWD VARCHAR(20) NULL, USER_GRADE VARCHAR(10) NULL, USER_GENDER VARCHAR(2) NULL, FAVORITE_CD VARCHAR(10) NULL, FAVORITE_NM VARCHAR(10) NULL, PHONE VARCHAR(20) NULL, EMAIL VARCHAR(50) NULL, ADDRESS VARCHAR(50) NULL, INPUT_DT Date NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS Member_Grade;

CREATE TABLE Member_Grade ( USER_GRADE VARCHAR(10) NOT NULL, USER_ID VARCHAR(10) NOT NULL, USER_GRADE_NM VARCHAR(10) NULL );

DROP TABLE IF EXISTS Magazine;

CREATE TABLE Magazine ( MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, CATEGORY_NM VARCHAR(10) NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL, DEL_YN CHAR(1) NULL );

DROP TABLE IF EXISTS Magagine_Attach;

CREATE TABLE Magagine_Attach ( ATTACH_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, ATTACH_TYPE VARCHAR(10) NULL, ATTACH_DIR VARCHAR(10) NULL, DEL_YN CHAR(1) NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS Animals;

CREATE TABLE Animals ( ANIMAL_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS ARTS _ ENTERTAINMENT;

CREATE TABLE ARTS _ ENTERTAINMENT ( AE_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS DESTINATIONS;

CREATE TABLE DESTINATIONS ( DESTINATION_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS HISTORY;

CREATE TABLE HISTORY ( HISTORY_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS LIFESTYLE;

CREATE TABLE LIFESTYLE ( LIFESTYLE_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS NATURE;

CREATE TABLE NATURE ( NATURE_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

DROP TABLE IF EXISTS PEOPLE;

CREATE TABLE PEOPLE ( PEOPLE_ID VARCHAR(10) NOT NULL, MEGAZINE_ID Number(5) NOT NULL, CATEGORY_ID VARCHAR(10) NOT NULL, TITLE VARCHAR(10) NULL, CONTENT TEXT NULL, REG_USER_ID VARCHAR(10) NULL, REG_DT Date NULL, MOD_USER_ID VARCHAR(10) NULL, MOD_DT Date NULL );

ALTER TABLE Member ADD CONSTRAINT PK_MEMBER PRIMARY KEY ( USER_ID );

ALTER TABLE Member_Grade ADD CONSTRAINT PK_MEMBER_GRADE PRIMARY KEY ( USER_GRADE, USER_ID );

ALTER TABLE Magazine ADD CONSTRAINT PK_MAGAZINE PRIMARY KEY ( MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE Magagine_Attach ADD CONSTRAINT PK_MAGAGINE_ATTACH PRIMARY KEY ( ATTACH_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE Animals ADD CONSTRAINT PK_ANIMALS PRIMARY KEY ( ANIMAL_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE ARTS _ ENTERTAINMENT ADD CONSTRAINT PK_ARTS _ ENTERTAINMENT PRIMARY KEY ( AE_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE DESTINATIONS ADD CONSTRAINT PK_DESTINATIONS PRIMARY KEY ( DESTINATION_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE HISTORY ADD CONSTRAINT PK_HISTORY PRIMARY KEY ( HISTORY_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE LIFESTYLE ADD CONSTRAINT PK_LIFESTYLE PRIMARY KEY ( LIFESTYLE_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE NATURE ADD CONSTRAINT PK_NATURE PRIMARY KEY ( NATURE_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY ( PEOPLE_ID, MEGAZINE_ID, CATEGORY_ID );

ALTER TABLE Member_Grade ADD CONSTRAINT FK_Member_TO_Member_Grade_1 FOREIGN KEY ( USER_ID ) REFERENCES Member ( USER_ID );

ALTER TABLE Magagine_Attach ADD CONSTRAINT FK_Magazine_TO_Magagine_Attach_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE Magagine_Attach ADD CONSTRAINT FK_Magazine_TO_Magagine_Attach_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE Animals ADD CONSTRAINT FK_Magazine_TO_Animals_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE Animals ADD CONSTRAINT FK_Magazine_TO_Animals_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE ARTS _ ENTERTAINMENT ADD CONSTRAINT FK_Magazine_TO_ARTS _ ENTERTAINMENT_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE ARTS _ ENTERTAINMENT ADD CONSTRAINT FK_Magazine_TO_ARTS _ ENTERTAINMENT_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE DESTINATIONS ADD CONSTRAINT FK_Magazine_TO_DESTINATIONS_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE DESTINATIONS ADD CONSTRAINT FK_Magazine_TO_DESTINATIONS_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE HISTORY ADD CONSTRAINT FK_Magazine_TO_HISTORY_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE HISTORY ADD CONSTRAINT FK_Magazine_TO_HISTORY_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE LIFESTYLE ADD CONSTRAINT FK_Magazine_TO_LIFESTYLE_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE LIFESTYLE ADD CONSTRAINT FK_Magazine_TO_LIFESTYLE_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE NATURE ADD CONSTRAINT FK_Magazine_TO_NATURE_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE NATURE ADD CONSTRAINT FK_Magazine_TO_NATURE_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

ALTER TABLE PEOPLE ADD CONSTRAINT FK_Magazine_TO_PEOPLE_1 FOREIGN KEY ( MEGAZINE_ID ) REFERENCES Magazine ( MEGAZINE_ID );

ALTER TABLE PEOPLE ADD CONSTRAINT FK_Magazine_TO_PEOPLE_2 FOREIGN KEY ( CATEGORY_ID ) REFERENCES Magazine ( CATEGORY_ID );

[KimYeounHong97]

set global max_connections=500; show status where variable_name = 'Threads_connected';