ERD
테이블 생성
# 휴일
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 NOT NULL,
`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 NOT NULL,
`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 NOT NULL,
`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 NOT NULL,
`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 NOT NULL,
`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,
`work_group_record_id` bigint NOT NULL,
PRIMARY KEY (settlement_id)
);
# 회사
CREATE TABLE `company`
(
`company_id` bigint auto_increment NOT NULL,
`name` varchar(50) NOT NULL,
`code` varchar(255) 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 `work_group_record`
(
`work_group_record_id` bigint auto_increment NOT NULL,
`date` datetime 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,
`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_group_record_id)
);
# 배포
CREATE TABLE `distribution`
(
`distribution_id` bigint auto_increment NOT NULL,
`date` datetime NOT NULL,
`user_id` bigint NOT NULL,
`work_group_id` bigint NOT NULL,
PRIMARY KEY (distribution_id)
);
# 회원
CREATE TABLE `user`
(
`user_id` bigint auto_increment NOT NULL,
`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) NULL,
`distribution` boolean NOT NULL,
`date_created` datetime NOT NULL,
`date_updated` datetime NOT NULL,
`date_deleted` datetime NULL,
`is_deleted` boolean NOT NULL,
`role` varchar(10) NOT NULL,
`company_id` bigint NOT NULL,
PRIMARY KEY (user_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_work_group_record_TO_settlement_1` FOREIGN KEY (
`work_group_record_id`
)
REFERENCES `work_group_record` (
`work_group_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`
);
샘플 데이터
# 회사
INSERT INTO `company` (`name`, `code`, `date_created`, `date_updated`, `is_deleted`)
VALUES ('더존비즈온', 'DZ1234', NOW(), NOW(), false);
# 사용자
INSERT INTO `user` (
`name`,
`email`,
`password`,
`phone`,
`hire_date`,
`birthday`,
`dept`,
`position`,
`distribution`,
`date_created`,
`date_updated`,
`is_deleted`,
`role`,
`company_id`
)
VALUES (
'테스트용',
'[email protected]',
'$2a$10$qY6DuWcrl/y5hJjWszBz/uVGvPF0ivxM2v.d5b66I/MxzGGBIhxBm',
'010-1234-5678',
'2023-01-01',
'1990-01-01',
'IT',
'대리',
false,
NOW(),
NOW(),
false,
'USER',
1
);