ERD Cloud에서 작업한 ERD
MySQL Workbench에서 작업한 DDL
Create database fandom_k;
USE fandom_k;
CREATE TABLE `idol_info` (
`MEMBER_ID` INT AUTO_INCREMENT NOT NULL,
`member_name` VARCHAR(50) NOT NULL,
`group_name` VARCHAR(50) NOT NULL,
`agency` VARCHAR(50) NOT NULL,
`member_photo` VARCHAR(50) NOT NULL,
`group_gen` INT NOT NULL,
`member_nation_code` VARCHAR(50) NOT NULL,
`idol_group_gender` CHAR(1) NOT NULL,
PRIMARY KEY (`MEMBER_ID`)
);
CREATE TABLE `user_info` (
`user_ID` INT auto_increment NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`user_nickname` varchar(10) NOT NULL,
`user_nation_code` varchar(50) NOT NULL,
`user_name` varchar(50) NOT NULL,
`user_gender` enum('M', 'F') NOT NULL,
`user_phone_number` varchar(50) NOT NULL,
`user_credit` float NOT NULL,
PRIMARY KEY (`user_ID`)
);
CREATE TABLE `donation` (
`donation_log` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`MEMBER_ID` int NOT NULL,
`category` varchar(50) NOT NULL,
`title` varchar(255) NOT NULL,
`target_credit` int NOT NULL,
`donation_start_date` datetime NOT NULL,
`donation_end_date` datetime NULL,
primary key (`donation_log`)
);
ALTER TABLE donation
ADD CONSTRAINT
FOREIGN KEY donation(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE donation
ADD CONSTRAINT
FOREIGN KEY (MEMBER_ID)
REFERENCES idol_info(MEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `Buy_credit` (
`buy_credit_log` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`buy_quantity` int NOT NULL,
`pay_cost` float NOT NULL,
`deposit_card` varchar(20) NOT NULL,
primary key (`buy_credit_log`)
);
ALTER TABLE Buy_credit
ADD CONSTRAINT fk_buy_credit_id
FOREIGN KEY Buy_credit(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `Artist_Of_The_Month` (
`AOTM_num` int auto_increment NOT NULL,
`MEMBER_ID` int NOT NULL,
`user_ID` int NOT NULL,
`vote_count` int NOT NULL,
`vote_date` datetime NOT NULL,
`aotm_total_credit` int NOT NULL,
primary key(`AOTM_num`)
);
ALTER TABLE Artist_Of_The_Month
ADD CONSTRAINT
FOREIGN KEY Artist_Of_The_Month(MEMBER_ID)
REFERENCES idol_info(MEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE Artist_Of_The_Month
ADD CONSTRAINT
FOREIGN KEY (user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `deposit` (
`deposit_buy_num` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`buy_credit_log` int NOT NULL,
`deposit_time` date NOT NULL,
`account_num` int NOT NULL,
primary key (`deposit_buy_num`)
);
ALTER TABLE deposit
ADD CONSTRAINT
FOREIGN KEY deposit(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE deposit
ADD CONSTRAINT
FOREIGN KEY (buy_credit_log)
REFERENCES Buy_credit(buy_credit_log)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `card` (
`card_buy_num` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`buy_credit_log` int NOT NULL,
`card_buy_time` date NOT NULL,
`card_company` varchar(255) NOT NULL,
`card_num` int NOT NULL,
primary key (`card_buy_num`)
);
ALTER TABLE card
ADD CONSTRAINT
FOREIGN KEY card(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE card
ADD CONSTRAINT
FOREIGN KEY (buy_credit_log)
REFERENCES Buy_credit(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `interest_artist` (
`MEMBER_ID` int NOT NULL,
`user_ID` int NOT NULL,
`interest_choose_date` datetime NOT NULL,
`interest_delete_date` datetime NULL
);
ALTER TABLE interest_artist
ADD CONSTRAINT
FOREIGN KEY interest_artist(MEMBER_ID)
REFERENCES idol_info(MEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE interest_artist
ADD CONSTRAINT
FOREIGN KEY (user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `use_credit` (
`vote_use_credit_log` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`AOTM_num` int NOT NULL,
`credit_use_date` datetime NOT NULL,
`use_credit_quantity` int NOT NULL,
primary key (`vote_use_credit_log`)
);
ALTER TABLE use_credit
ADD CONSTRAINT
FOREIGN KEY use_credit(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE use_credit
ADD CONSTRAINT
FOREIGN KEY (AOTM_num)
REFERENCES Artist_OF_The_Month(AOTM_num)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `idol_news` (
`news_num` int auto_increment NOT NULL,
`MEMBER_ID` int NOT NULL,
`admin_id` int NOT NULL,
`news_category` varchar(30) NOT NULL,
`news_title` varchar(255) NOT NULL,
`news_content` text NOT NULL,
`news_publish_date` datetime NOT NULL,
`news_final_edit_date` datetime NOT NULL,
primary key (`news_num`)
);
ALTER TABLE idol_news
ADD CONSTRAINT
FOREIGN KEY idol_news(MEMBER_ID)
REFERENCES idol_info(MEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE idol_news
ADD CONSTRAINT
FOREIGN KEY (admin_id)
REFERENCES fandom_k_admin(admin_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `fandom_k_admin` (
`admin_id` int NOT NULL,
`login_id` varchar(20) NOT NULL,
`login_password` varchar(20) NOT NULL,
`login_time` datetime NOT NULL,
`logout_time` datetime NOT NULL,
primary key (`admin_id`)
);
CREATE TABLE `donate_update` (
`donate_update_id` int auto_increment NOT NULL,
`user_ID` int NOT NULL,
`donation_log` int NOT NULL,
`donate_datetime` datetime NOT NULL,
`donate_credit` int NOT NULL,
`donate_total_credit` int NOT NULL,
`target_success_fail` char(1) NOT NULL,
primary key (`donate_update_id`)
);
ALTER TABLE donate_update
ADD CONSTRAINT
FOREIGN KEY donate_update(user_id)
REFERENCES user_info(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE donate_update
ADD CONSTRAINT
FOREIGN KEY (user_id)
REFERENCES donation(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
CREATE TABLE `admin_entity` (
`admin_id` int NOT NULL,
`user_ID` int NOT NULL,
`MEMBER_ID` int NOT NULL,
`donation_log` int NOT NULL,
`AOTM_num` int NOT NULL,
`buy_credit_log` int NOT NULL
);
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY `admin_entity`(admin_id)
REFERENCES `fandom_k_admin`(admin_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY (user_id)
REFERENCES `user_info`(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY (MEMBER_ID)
REFERENCES `idol_info`(MEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY (donation_log)
REFERENCES `donation`(donation_log)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY (AOTM_num)
REFERENCES `Artist_OF_The_Month`(AOTM_num)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `admin_entity`
ADD CONSTRAINT
FOREIGN KEY (buy_credit_log)
REFERENCES `buy_credit`(buy_credit_log)
ON DELETE CASCADE
ON UPDATE CASCADE;위 코드 기반 워크벤치 ERD
Yoochang Sung