[미션17] Fandom-K 서비스 DB 구축하기

 

Fandom-K는 K-pop 아티스트와 팬을 이어주는 글로벌 조공 플랫폼으로, 해당 서비스의 DB 구축해보는 미션

 

[내가 좋아하는 아이돌을 가장 쉽게 덕질하는 방법, Fandom-K]

 

주요 기능

 

1.후원하기

 - 크레딧은 Fandom-K에서 사용하는 가상 화폐

 - 소유하고 있는 크레딧을 유저가 원하는 조공에 후원 가능

 - 후원할 크레딧은 유저가 보유하고 있는 크레딧 내에서 자유롭게 결정 가능

 - 여러 유저의 후원을 통해 크레딧 목표치가 달성되면 조공 이루어짐

 

2. 이달의 아티스트 투표하기

 - 카테고리 별로 유저가 원하는 아티스트에게 투표 가능

 - 한 번 투표하는 데 일정 크레딧 소모 ( 예시 화면 : 1000크레딧 소모 ) 

 - 한 유저가 여러 아티스트에게 투표 가능, 투표 횟수 무제한

 

3. 나만의 아티스트

 - 마이페에지에서 좋아하는 아티스트 팔로우 가능 (여러 명 가능)

 - 팔로우한 아티스트의 여러 소식을 메인 화면에서 확인 가능

 

4. 충전하기

 - 원하는 만큼 크레딧 충전 가능

 - Fandom-K의 주요 매출은 크레딧 판매 수수료로 발생

 

---

 

 

1. ERD Cloud 캡쳐본

 

2. SQL DDL문

CREATE DATABASE FandomK;

USE FandomK;

CREATE TABLE `user` (
    user_id     INT            NOT NULL,
    `name`      VARCHAR(10)    NOT NULL,
    gender      ENUM('m','f')  NOT NULL,
    birthday    DATETIME       NOT NULL,
    address     TEXT           NOT NULL,
    mail        TEXT           NOT NULL,
    sign_up_day DATETIME       NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE artist (
    artist_id   INT            NOT NULL,
    `name`      VARCHAR(10)    NOT NULL,
    `group`     VARCHAR(20)    NULL,
    company     VARCHAR(20)    NOT NULL,
    real_name   VARCHAR(20)    NOT NULL,
    gender      ENUM('m','f')  NOT NULL,
    birthday    DATETIME       NOT NULL,
    PRIMARY KEY (artist_id)
);

CREATE TABLE favorite_artist (
    user_id        INT                          NOT NULL,
    artist_id      INT                          NOT NULL,
    artist_news_id INT                          NOT NULL,
    created_date   DATETIME                     NOT NULL,
    category       ENUM('관심추가','관심해제')   NOT NULL
); 


CREATE TABLE artist_news (
    artist_news_id  INT            NOT NULL,
    artist_id       INT            NOT NULL,
    title           VARCHAR(300)   NOT NULL,
    content         TEXT           NOT NULL,
    created_date    DATETIME       NOT NULL,
    update_date     DATETIME       NOT NULL
);


CREATE TABLE credit_now (
    credit_now_id  INT       NOT NULL,
    user_id        INT       NOT NULL,
    now_amount     INT       NOT NULL,
    created_date   DATETIME  NOT NULL,
    update_date    DATETIME  NOT NULL
);


CREATE TABLE credit_charge (
    credit_cg_id    INT                   NOT NULL,
    user_id         INT                   NOT NULL,
    credit_now_id   INT                   NOT NULL,
    cg_amount       INT                   NOT NULL,
    payment         INT                   NOT NULL,
    pay_method      ENUM('card','cash')   NOT NULL,
    `date`          DATETIME              NOT NULL
);


CREATE TABLE credit_use (
    credit_use_id   INT                     NOT NULL,
    user_id         INT                     NOT NULL,
    credit_now_id   INT                     NOT NULL,
    used_amount     INT                     NOT NULL,
    purpose         ENUM('support','vote')  NOT NULL,
    use_date        DATETIME                NOT NULL
);


CREATE TABLE sales (
    sales_id     INT   NOT NULL,
    credit_cg_id INT   NOT NULL,
    pay_range    INT   NOT NULL,
    percent      FLOAT NOT NULL,
    sales        INT   NOT NULL
);


CREATE TABLE support (
    support_id     INT  NOT NULL,
    ad_id          INT  NOT NULL,
    user_id        INT  NOT NULL,
    credit_use_id  INT  NOT NULL
);


CREATE TABLE vote (
    vote_id          INT  NOT NULL,
    chart_id         INT  NOT NULL,
    user_id          INT  NOT NULL,
    artist_id        INT  NOT NULL,
    credit_use_id    INT  NOT NULL,
    vote_summary_id  INT  NOT NULL
);


CREATE TABLE vote_summary (
    vote_summary_id   INT NOT NULL,
    artist_id         INT NOT NULL,
    vote_counts       INT NOT NULL,
    vote_rank         INT NOT NULL,
    `year`            INT NOT NULL,
    `month`           INT NOT NULL
);


CREATE TABLE advertisement (
    ad_id               INT                                                 NOT NULL,
    artist_id           INT                                                 NOT NULL,
    title               TEXT                                                NULL,
    category            ENUM('지하철광고','생일카페','커피차','간식차','기타') NOT NULL,
    total_credit_target INT                                                 NOT NULL,
    deadline_date       DATETIME                                            NOT NULL,
    total_credit        INT                                                 NOT NULL,
    `status`            ENUM('모금중','조공완료','달성실패')                  NOT NULL
);


CREATE TABLE charts (
    chart_id  INT                                                             NOT NULL,
    category  ENUM('이달의 여자 아이돌','이달의 남자 아이돌','이달의 그룹 아이돌') NOT NULL
);


-- favorite_artist pk 설정
ALTER TABLE favorite_artist 
ADD CONSTRAINT `PK_favotie_artist` 
PRIMARY KEY (user_id, artist_id);

-- artist_news pk 설정 
ALTER TABLE artist_news
ADD CONSTRAINT `PK_artist_news` 
PRIMARY KEY (artist_news_id);

-- credit_now pk 설정
ALTER TABLE credit_now
ADD CONSTRAINT `PK_credit_now` 
PRIMARY KEY (credit_now_id, user_id);

-- credit_charge pk 설정
ALTER TABLE credit_charge
ADD CONSTRAINT `PK_credit_charge` 
PRIMARY KEY (credit_cg_id);

-- credit_use pk 설정
ALTER TABLE credit_use
ADD CONSTRAINT `PK_credit_use` 
PRIMARY KEY (credit_use_id);

-- sales pk 설정
ALTER TABLE sales
ADD CONSTRAINT `PK_sales` 
PRIMARY KEY (sales_id,credit_cg_id);

-- support pk 설정
ALTER TABLE support
ADD CONSTRAINT `PK_support` 
PRIMARY KEY (support_id, ad_id);

-- vote pk 설정
ALTER TABLE vote
ADD CONSTRAINT `PK_vote` 
PRIMARY KEY (vote_id, chart_id);

-- vote_summary pk 설정
ALTER TABLE vote_summary
ADD CONSTRAINT `PK_vote_summary` 
PRIMARY KEY (vote_summary_id);

-- advertisement pk 설정
ALTER TABLE advertisement
ADD CONSTRAINT `PK_advertisement` 
PRIMARY KEY (ad_id);

-- charts pk 설정
ALTER TABLE charts
ADD CONSTRAINT `PK_charts` 
PRIMARY KEY (chart_id);



-- favorite_artist fk 설정
ALTER TABLE favorite_artist 
ADD CONSTRAINT `FK_user_to_favorite_artist_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);

ALTER TABLE favorite_artist 
ADD CONSTRAINT `FK_artist_TO_favorite_artist_2` 
FOREIGN KEY (artist_id)
REFERENCES artist (artist_id);

ALTER TABLE favorite_artist 
ADD CONSTRAINT `FK_artist_TO_favorite_artist_3` 
FOREIGN KEY (artist_news_id)
REFERENCES artist_news (artist_news_id);


-- artist_news fk 설정 
ALTER TABLE artist_news
ADD CONSTRAINT `FK_artist_TO_artist_news_1` 
FOREIGN KEY (artist_id)
REFERENCES artist (artist_id);


-- credit_now fk 설정
ALTER TABLE credit_now 
ADD CONSTRAINT `FK_user_TO_credit_now_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);


-- credit_charge fk 설정
ALTER TABLE credit_charge 
ADD CONSTRAINT `FK_user_TO_credit_charge_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);

ALTER TABLE credit_charge 
ADD CONSTRAINT `FK_credit_now_TO_credit_charge_1` 
FOREIGN KEY (credit_now_id)
REFERENCES credit_now (credit_now_id);


-- credit_use fk 설정
ALTER TABLE credit_use 
ADD CONSTRAINT `FK_user_TO_credit_use_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);

ALTER TABLE credit_use 
ADD CONSTRAINT `FK_credit_now_TO_credit_use_1` 
FOREIGN KEY (credit_now_id)
REFERENCES credit_now (credit_now_id);


-- sales fk 설정
ALTER TABLE sales 
ADD CONSTRAINT `FK_credit_charge_TO_sales_1` 
FOREIGN KEY (credit_cg_id)
REFERENCES credit_charge (credit_cg_id);


-- support fk 설정
ALTER TABLE support 
ADD CONSTRAINT `FK_ad_TO_support_1` 
FOREIGN KEY (ad_id)
REFERENCES advertisement (ad_id);

ALTER TABLE support  
ADD CONSTRAINT `FK_user_TO_support_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);

ALTER TABLE support  
ADD CONSTRAINT `FK_credit_use_TO_support_1` 
FOREIGN KEY (credit_use_id)
REFERENCES credit_use (credit_use_id);


-- vote fk 설정
ALTER TABLE vote 
ADD CONSTRAINT `FK_charts_TO_vote_1` 
FOREIGN KEY (chart_id)
REFERENCES charts (chart_id);

ALTER TABLE vote 
ADD CONSTRAINT `FK_user_TO_vote_1` 
FOREIGN KEY (user_id)
REFERENCES `user` (user_id);

ALTER TABLE vote 
ADD CONSTRAINT `FK_artist_TO_vote_1` 
FOREIGN KEY (artist_id)
REFERENCES artist (artist_id);

ALTER TABLE vote 
ADD CONSTRAINT `FK_credit_use_TO_vote_1` 
FOREIGN KEY (credit_use_id)
REFERENCES credit_use (credit_use_id);

ALTER TABLE vote 
ADD CONSTRAINT `FK_vote_summary_TO_vote_1` 
FOREIGN KEY (vote_summary_id)
REFERENCES vote_summary (vote_summary_id);


-- vote_summary fk 설정
ALTER TABLE vote_summary
ADD CONSTRAINT `FK_artist_TO_vote_summary_1`
 FOREIGN KEY (artist_id)
REFERENCES artist (artist_id);


-- advertisement fk 설정
ALTER TABLE advertisement 
ADD CONSTRAINT `FK_artist_TO_advertisement_1` 
FOREIGN KEY (artist_id)
REFERENCES artist (artist_id);

 

3. MySQL ERD 캡쳐본