MySQL Server 통일

[yoopark]

@minchan02 @dong97338
#31 민찬님이 현재 사용하고 계시는 MySQL Server를, 이제 팀 단위에서 접근하도록 관리해야 할 것 같습니다.
Azure에 Server를 만들려하였지만 사용시간이 제한적이기 때문에 그 외 시간에는 직접 테스트를 해볼 수 없다는 한계점이 존재하여,
직접 Goorm IDE에 Server 컨테이너를 하나 만들었습니다 👍
테스트 결과 원격 접속도 문제없이 실행되어 사용하기에 문제 없을 것으로 생각됩니다.

이번 이슈를 통하여 데이터 명세를 통일하고자 합니다!
민찬님, 동현님도 혹시 따로 사용하고 계시는 DB가 있다면 이 이슈에서 말씀해주세요.

현재 제가 만든 miliroutine_db는 #8 을 기반으로 하여 user의 level 정보, category_list 분리 등의 사소한 부분만 수정된 상황입니다.

CREATE DATABASE miliroutine_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE miliroutine_db;

CREATE TABLE user (
    no INT UNSIGNED AUTO_INCREMENT,
    id VARCHAR(30),
    pw VARCHAR(100) NOT NULL,
    salt VARCHAR(64) NOT NULL,
    email VARCHAR(60) NOT NULL,
    nickname VARCHAR(10) NOT NULL,
    profile_img VARCHAR(300),
    background_img VARCHAR(300),
    point INT UNSIGNED DEFAULT 0,
    exp INT UNSIGNED DEFAULT 0,
    PRIMARY KEY (no)
);

CREATE TABLE user_category (
    id INT UNSIGNED AUTO_INCREMENT,
    user_no INT UNSIGNED,
    category VARCHAR(20) NOT NULL, # study, workout, morningroutine, economy, selfcare, dream, hobby, emotion, health
    PRIMARY KEY (id),
    FOREIGN KEY (user_no) REFERENCES user(no)
);

CREATE TABLE level_exp (
    level TINYINT UNSIGNED,
    exp INT UNSIGNED NOT NULL,
    PRIMARY KEY (level)
);

CREATE TABLE routine (
    id INT UNSIGNED AUTO_INCREMENT,
    host INT UNSIGNED,
    name VARCHAR(20) NOT NULL,
    category VARCHAR(20) NOT NULL,
    thumbnail_img VARCHAR(300),
    auth_cycle  TINYINT UNSIGNED NOT NULL,
    auth_description_list JSON NOT NULL, # ["매일 저녁 감사한 일을 생각해보세요", "해당 내용을 [인증하기] 탭에 기록하여 업로드하면 참여 완료!\n(업로드한 글은 다른 사람에게 공개되지 않습니다)"]
    start_date DATE NOT NULL, # yyyy-mm-dd
    duration TINYINT UNSIGNED NOT NULL,
    point_info_list JSON, # [{'type': "every_week", 'point': 20}, {'type': "rate", 'number': 0.5, 'point': 100}, {'type': "rate", 'number': 0.9, 'point': 100}]
    PRIMARY KEY (id),
    FOREIGN KEY (host) REFERENCES user(no)
);

CREATE TABLE user_routine (
    id INT UNSIGNED AUTO_INCREMENT,
    user_no INT UNSIGNED,
    routine_id INT UNSIGNED,
    type VARCHAR(10) NOT NULL, # join, like
    PRIMARY KEY (id),
    FOREIGN KEY (user_no) REFERENCES user(no),
    FOREIGN KEY (routine_id) REFERENCES routine(id)
);


CREATE TABLE auth (
    id INT UNSIGNED AUTO_INCREMENT,
    user_no INT UNSIGNED,
    routine_id INT UNSIGNED,
    week TINYINT UNSIGNED NOT NULL,
    day TINYINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    img VARCHAR(300),
    text TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (user_no) REFERENCES user(no),
    FOREIGN KEY (routine_id) REFERENCES routine(id)
);

CREATE TABLE goods (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    description TEXT NOT NULL,
    thumbnail_img VARCHAR(300),
    price INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE user_goods (
    id INT UNSIGNED AUTO_INCREMENT,
    user_no INT UNSIGNED,
    goods_id INT UNSIGNED,
    datetime DATETIME NOT NULL, # yyyy-mm-dd hh:mm:ss
    PRIMARY KEY (id),
    FOREIGN KEY (user_no) REFERENCES user(no),
    FOREIGN KEY (goods_id) REFERENCES goods(id)
);

INSERT INTO level_exp
	VALUES(1, 100), (2, 100+500), (3, 600+1000), (4, 1600+2000), (5, 3600+2000), (6, 5600+2000), (7, 7600+5000), (8, 12600+5000), (9, 17600+5000);

P.S. 접속 방법과 계정 정보는 Slack을 참고해주시기 바랍니다.
P.S. Notion DATABASE : miliroutine_db 글은 이 이슈 종료 이후 한번에 수정하겠습니다!

[yoopark]

현재 Dummy Data를 일차적으로 담아놓았습니다.

Dataset 크기

user : 100개
user_category : 400개
routine : 30개
user_routine : 400개
auth : 1000개
goods : 10개
user_goods : 200개

Query 예시

SELECT * FROM user_category
	WHERE user_no = 2;

SELECT name FROM routine;

SELECT * FROM user_routine
	WHERE routine_id = 20 AND type = "join";

SELECT week, day, date, img, text FROM auth
	WHERE routine_id = 1 AND user_no = 2; # auth Dummy를 1000개까지밖에 못 만드는 관계로, routine_id를 1~3만 고를 수 있도록 줄여버렸습니다. 

SELECT goods_id, datetime FROM user_goods
	WHERE user_no = 2 ORDER BY datetime;

[minchan02]

혹시 routine table의 host 필드는 어떤걸 저장하는 필든가요?

[yoopark]

@minchan02 루틴 개설 유저 no입니다.