ERD

테이블 생성

# 회원
CREATE TABLE `user`
(
    `user_id`      bigint AUTO_INCREMENT,
    `name`         varchar(50)  NOT NULL,
    `email`        varchar(100) NOT NULL,
    `password`     varchar(255) NOT NULL,
    `phone`        varchar(20)  NOT NULL,
    `hire_date`    date         NULL,
    `birthday`     date         NOT NULL,
    `dept`         varchar(50)  NULL,
    `position`     varchar(20)  NOT NULL,
    `gender`       char(1)      NOT NULL,
    `distribution` boolean      NOT NULL,
    `date_created` datetime     NOT NULL,
    `date_updated` datetime     NOT NULL,
    `date_deleted` datetime     NULL,
    `is_deleted`   boolean      NOT NULL,
    `is_admin`     boolean      NOT NULL,
    `company_id`   bigint       NOT NULL,
    PRIMARY KEY (user_id)
);

# 회사 휴일
CREATE TABLE `holiday`
(
    `holiday_id` bigint AUTO_INCREMENT,
    `date`       date        NOT NULL,
    `name`       varchar(50) NOT NULL,
    `pay_type`   varchar(10) NOT NULL,
    `company_id` bigint      NOT NULL,
    PRIMARY KEY (holiday_id)
);

# 출퇴근기록
CREATE TABLE `time_record`
(
    `time_record_id` bigint AUTO_INCREMENT,
    `user_id`        bigint      NOT NULL,
    `date`           date        NOT NULL,
    `work`           time        NOT NULL,
    `leave`          time        NOT NULL,
    `work_state`     varchar(10) NOT NULL,
    `date_created`   datetime    NOT NULL,
    `date_updated`   datetime    NOT NULL,
    PRIMARY KEY (time_record_id)
);

# 근로제
CREATE TABLE `work_group`
(
    `work_group_id` bigint AUTO_INCREMENT,
    `name`          varchar(50) NOT NULL,
    `type`          char(2)     NOT NULL,
    `date_created`  datetime    NOT NULL,
    `date_updated`  datetime    NOT NULL,
    `date_deleted`  datetime    NULL,
    `is_deleted`    boolean     NOT NULL,
    `is_on`         boolean     NOT NULL,
    `company_id`    bigint      NOT NULL,
    PRIMARY KEY (work_group_id)
);

# 시간범위
CREATE TABLE `time_range`
(
    `time_range_id` bigint AUTO_INCREMENT,
    `type`          char(2) NOT NULL,
    `start`         time    NOT NULL,
    `end`           time    NOT NULL,
    `work_group_id` bigint  NOT NULL,
    PRIMARY KEY (time_range_id)
);

# 근무요일타입
CREATE TABLE `work_day_type`
(
    `work_day_type_id` bigint AUTO_INCREMENT,
    `mon`              char(2) NOT NULL,
    `tue`              char(2) NOT NULL,
    `wed`              char(2) NOT NULL,
    `thu`              char(2) NOT NULL,
    `fri`              char(2) NOT NULL,
    `sat`              char(2) NOT NULL,
    `sun`              char(2) NOT NULL,
    `work_group_id`    bigint  NOT NULL,
    PRIMARY KEY (work_day_type_id)
);

# 배포이력
CREATE TABLE `distribution_record`
(
    `distribution_record_id` bigint AUTO_INCREMENT,
    `date`                   datetime     NOT NULL,
    `user_id`                bigint       NOT NULL,
    `work_group_name`        varchar(50)  NOT NULL,
    `work_group_type`        char(2)      NOT NULL,
    `time_range_type`        varchar(255) NOT NULL,
    `start`                  varchar(255) NOT NULL,
    `end`                    varchar(255) NOT NULL,
    PRIMARY KEY (distribution_record_id)
);

# 정산
CREATE TABLE `settlement`
(
    `settlement_id`          bigint AUTO_INCREMENT,
    `user_id`                bigint   NOT NULL,
    `date`                   date     NOT NULL,
    `working_time`           time     NOT NULL,
    `overtime`               time     NOT NULL,
    `day_type`               char(2)  NOT NULL,
    `date_created`           datetime NOT NULL,
    `date_updated`           datetime NOT NULL,
    `distribution_record_id` bigint   NOT NULL,
    PRIMARY KEY (settlement_id)
);

# 배포
CREATE TABLE `distribution`
(
    `distribution_id` bigint AUTO_INCREMENT,
    `date`            datetime NOT NULL,
    `user_id`         bigint   NOT NULL,
    `work_group_id`   bigint   NOT NULL,
    PRIMARY KEY (distribution_id)
);

# 회사
CREATE TABLE `company`
(
    `company_id`   bigint AUTO_INCREMENT,
    `name`         varchar(50) NOT NULL,
    `date_created` datetime    NOT NULL,
    `date_updated` datetime    NOT NULL,
    `date_deleted` datetime    NULL,
    `is_deleted`   boolean     NOT NULL,
    PRIMARY KEY (company_id)
);

# 제약조건
ALTER TABLE `holiday`
    ADD CONSTRAINT `FK_company_TO_holiday_1` FOREIGN KEY (`company_id`)
        REFERENCES `company` (`company_id`);

ALTER TABLE `work_group`
    ADD CONSTRAINT `FK_company_TO_work_group_1` FOREIGN KEY (`company_id`)
        REFERENCES `company` (`company_id`);

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

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

ALTER TABLE `settlement`
    ADD CONSTRAINT `FK_distribution_record_TO_settlement_1` FOREIGN KEY (`distribution_record_id`)
        REFERENCES `distribution_record` (`distribution_record_id`);

ALTER TABLE `distribution`
    ADD CONSTRAINT `FK_user_TO_distribution_1` FOREIGN KEY (`user_id`)
        REFERENCES `user` (`user_id`);

ALTER TABLE `distribution`
    ADD CONSTRAINT `FK_work_group_TO_distribution_1` FOREIGN KEY (`work_group_id`)
        REFERENCES `work_group` (`work_group_id`);

ALTER TABLE `user`
    ADD CONSTRAINT `FK_company_TO_user_1` FOREIGN KEY (`company_id`)
        REFERENCES `company` (`company_id`);