[DA] 스프린트 미션 8

1) Fandom-K ERD 

 

2) SQL문

CREATE DATABASE Fandom_K;

USE Fandom_K;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    gender CHAR(2),
    signup_date DATETIME,
    birth_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE idols (
    idol_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    gorup VARCHAR(255),
    gender VARCHAR(10),
    agency VARCHAR(50),
    birthday DATE
);

CREATE TABLE follow (
    follow_id INT AUTO_INCREMENT PRIMARY KEY,
    idol_id INT,
    user_id INT,
    FOREIGN KEY (idol_id) REFERENCES idols(idol_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE votes (
    vote_id INT AUTO_INCREMENT PRIMARY KEY,
    idol_id INT,
    user_id INT,
    time DATETIME,
    start_date DATETIME,
    end_date DATETIME,
    FOREIGN KEY (idol_id) REFERENCES idols(idol_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE ads (
    ad_id INT AUTO_INCREMENT PRIMARY KEY,
    idol_id INT,
    name VARCHAR(255),
    start_date DATETIME,
    end_date DATETIME,
    donation INT,
    category VARCHAR(50),
    status VARCHAR(50),
    FOREIGN KEY (idol_id) REFERENCES idols(idol_id)
);

CREATE TABLE donations (
    donations_id INT AUTO_INCREMENT PRIMARY KEY,
    ad_id INT,
    user_id INT,
    amount INT,
    dona_date DATETIME,
    status VARCHAR(10),
    FOREIGN KEY (ad_id) REFERENCES ads(ad_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE payment_details (
    payment_details_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    paid_at DATETIME,
    provider VARCHAR(50),
    card INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE payments (
    charge_id INT AUTO_INCREMENT PRIMARY KEY,
    payment_details_id INT,
    amount INT,
    exchange INT,
    spread INT,
    charge_date DATETIME,
    charge_status VARCHAR(50),
    charge_time TIME,
    FOREIGN KEY (payment_details_id)
        REFERENCES payment_details(payment_details_id)
);

CREATE TABLE credit_ledger (
    credit_ledger_id INT AUTO_INCREMENT PRIMARY KEY,
    vote_id INT,
    donations_id INT,
    user_id INT,
    amount INT,
    type VARCHAR(50),
    created_at DATETIME,
    FOREIGN KEY (vote_id) REFERENCES votes(vote_id),
    FOREIGN KEY (donations_id) REFERENCES donations(donations_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE credit (
    credit_id INT AUTO_INCREMENT PRIMARY KEY,
    credit_ledger_id INT,
    charge_id INT,
    balance INT,
    FOREIGN KEY (credit_ledger_id) REFERENCES credit_ledger(credit_ledger_id),
    FOREIGN KEY (charge_id) REFERENCES payments(charge_id)
);

 

 

3) MySQL Workbench ERD