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         NOT NULL,
    `birthday`      date         NOT NULL,
    `dept_id`       bigint       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,
    `work_group_id` bigint       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,
    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(20)    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,
    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 `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,
    PRIMARY KEY (settlement_id)
);

# 회원-근로제 기록
CREATE TABLE `user_group_record`
(
    `user_group_record_id` bigint AUTO_INCREMENT,
    `time`                 date   NOT NULL,
    `user_id`              bigint NOT NULL,
    `work_group_id`        bigint NOT NULL,
    PRIMARY KEY (user_group_record_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)
);

# 부서권한
CREATE TABLE `dept_privilege`
(
    `user_id`        bigint   NOT NULL,
    `dept_id`        bigint   NOT NULL,
    `privilege_date` datetime NOT NULL,
    PRIMARY KEY (user_id, dept_id),
);

# 부서
CREATE TABLE `dept`
(
    `dept_id` bigint AUTO_INCREMENT,
    `name`    varchar(50) NOT NULL,
    PRIMARY KEY (dept_id)
);

# 제약조건
ALTER TABLE `user`
    ADD CONSTRAINT `FK_work_group_TO_user_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`);

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 `dept_privilege`
    ADD CONSTRAINT `FK_user_TO_dept_privilege_1` FOREIGN KEY (`user_id`)
        REFERENCES `user` (`user_id`);

ALTER TABLE `dept_privilege`
    ADD CONSTRAINT `FK_dept_TO_dept_privilege_1` FOREIGN KEY (`dept_id`)
        REFERENCES `dept` (`dept_id`);