ERD

테이블 생성

DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `holiday`;
DROP TABLE IF EXISTS `time_record`;
DROP TABLE IF EXISTS `work_group`;
DROP TABLE IF EXISTS `time_range`;
DROP TABLE IF EXISTS `work_day_type`;
DROP TABLE IF EXISTS `settlement`;
DROP TABLE IF EXISTS `work_record_restriction`;
DROP TABLE IF EXISTS `user_group_record`;

# 회원
CREATE TABLE `user`
(
    `user_id`            bigint AUTO_INCREMENT,
    `user_name`          varchar(20)  NOT NULL,
    `user_email`         varchar(100) NOT NULL,
    `user_password`      varchar(255) NOT NULL,
    `user_phone_number`  varchar(30)  NOT NULL,
    `user_role`          varchar(20)  NOT NULL,
    `user_join_company`  date         NOT NULL,
    `user_birthdate`     date         NOT NULL,
    `user_department`    varchar(30)  NOT NULL,
    `user_position`      varchar(30)  NOT NULL,
    `user_gender`        char         NOT NULL,
    `user_absence_count` int          NOT NULL,
    `work_group_id`      bigint       NOT NULL,
    PRIMARY KEY (user_id)
);

# 휴일
CREATE TABLE `holiday`
(
    `holiday_id`       bigint AUTO_INCREMENT,
    `holiday_date`     date        NOT NULL,
    `holiday_name`     varchar(50) NOT NULL,
    `holiday_pay_type` char(2)     NOT NULL,
    PRIMARY KEY (holiday_id)
);

# 시간범위
CREATE TABLE `time_record`
(
    `time_record_id`    bigint      NOT NULL,
    `time_record_date`  date        NOT NULL,
    `time_record_start` time        NOT NULL,
    `time_record_end`   time        NOT NULL,
    `user_work_state`   varchar(50) NOT NULL,
    `user_id`           bigint      NOT NULL,
    PRIMARY KEY (time_record_id)
);

# 근무그룹
CREATE TABLE `work_group`
(
    `work_group_id`   bigint      NOT NULL,
    `work_group_name` varchar(50) NOT NULL,
    `work_group_type` varchar(50) NOT NULL,
    PRIMARY KEY (work_group_id)
);

# 시간범위
CREATE TABLE `time_range`
(
    `time_record_id`   bigint NOT NULL,
    `time_range_type`  char   NOT NULL,
    `time_range_start` time   NOT NULL,
    `time_range_end`   time   NOT NULL,
    `work_group_id`    bigint NOT NULL,
    PRIMARY KEY (time_record_id)
);

# 근무요일타입
CREATE TABLE `work_day_type`
(
    `work_day_type_id`  bigint      NOT NULL,
    `work_day_type_mon` varchar(50) NOT NULL,
    `work_day_type_tue` varchar(50) NOT NULL,
    `work_day_type_wed` varchar(50) NOT NULL,
    `work_day_type_thu` varchar(50) NOT NULL,
    `work_day_type_fri` varchar(50) NOT NULL,
    `work_day_type_sat` varchar(50) NOT NULL,
    `work_day_type_sun` varchar(50) NOT NULL,
    `work_group_id`     bigint      NOT NULL,
    PRIMARY KEY (work_day_type_id)
);

# 정산
CREATE TABLE `settlement`
(
    `settlement_id`           bigint      NOT NULL,
    `settlement_date`         date        NOT NULL,
    `settlement_working_time` time        NOT NULL,
    `settlement_overtime`     time        NOT NULL,
    `settlement_day_type`     varchar(50) NOT NULL,
    `user_id`                 bigint      NOT NULL,
    PRIMARY KEY (settlement_id)
);

# 회원-근무그룹 기록
CREATE TABLE `user_group_record`
(
    `user_group_record_id`              bigint      NOT NULL,
    `user_group_record_ time`           date        NOT NULL,
    `user_group_record_user_name`       varchar(50) NOT NULL,
    `user_group_record_work_group_name` varchar(50) NOT NULL,
    `user_id`                           bigint      NOT NULL,
    PRIMARY KEY (user_group_record_id)
);

# 구현 미정
# 출근기록제한
# CREATE TABLE `work_record_restriction`
# (
#     `work_record_restriction_id`        bigint      NOT NULL,
#     `work_record_restriction_activated` boolean     NULL,
#     `work_record_restriction_method`    varchar(50) NULL,
#     PRIMARY KEY (work_record_restriction_id)
# );

# 외래키 제약조건 설정
ALTER TABLE `user`
    ADD FOREIGN KEY (`work_group_id`) REFERENCES `work_group` (`work_group_id`);

ALTER TABLE `time_record`
    ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `time_range`
    ADD FOREIGN KEY (`work_group_id`) REFERENCES `work_group` (`work_group_id`);

ALTER TABLE `work_day_type`
    ADD FOREIGN KEY (`work_group_id`) REFERENCES `work_group` (`work_group_id`);

ALTER TABLE `settlement`
    ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);

ALTER TABLE `USER_GROUP_RECORD`
    ADD FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);