DB 설계

DB 설계

1. 개체(Entity)와 속성(Attribute) 추출하기

• 담당자(manager) / 속성 : 담당자명, 직급, 전화번호
• 업무(task) / 속성 : 업무명, 업무 코드
• 업무종류(kind) / 속성 : 업무종류명
• 회사(company) / 속성 : 회사명

2. 개체간의 관계 추출하기

• 일대일(1:1), 일대다(1:N), 다대다(N:N) / 선택적인 관계, 필수적인 관계

• 담당자 - 업무 (1:N)

  • 한 명의 담당자는 하나의 업무를 가진다.

  • 하나의 업무는 여러 명의 담당자를 가진다.
    -> 일대다 관계
    -> 담당자 테이블에 외래키로 업무 id 추가

    

• 담당자 - 업무 종류 (N:M)

  • 한 명의 담당자는 여러 개의 업무 종류를 가진다.

  • 한 개의 업무 종류는 여러 명의 담당자를 가진다.

    -> 다대다 관계
    -> 중간에 관계 테이블을 추가한다.
    

• 담당자 - 회사 (N:M)

  • 한 명의 담당자는 여러 개의 회사를 가진다.

  • 한 개의 회사는 여러 명의 담당자를 가진다.

    -> 다대다 관계
    -> 중간에 관계 테이블을 추가한다.

    

• 전체적인 ERD 구조

-> 담당자를 마스터 테이블로 설정

My SQL에서 ERD 보는 방법 : My SQL Workbench에 접속해 Ctrl + R을 누름

SQL 작성

1. 테이블 생성

create table manager(
	id int auto_increment primary key,
    manager_name varchar(50) not null,
	position_type enum('선임','수석','책임') not null,
    phone_number varchar(50),
    task_id int,
    create_time timestamp default current_timestamp,
    update_time timestamp,
	foreign key(task_id)
    references task(id)
    on update cascade
    on delete cascade
);

create table task(
	id int primary key,
    task_code varchar(20) unique,
    task_name varchar(100),
    create_time timestamp default current_timestamp,
    update_time timestamp
);

create table kind(
	id int auto_increment primary key,
    kind_name varchar(100) not null,
    create_time timestamp default current_timestamp,
    update_time timestamp
);

create table company(
	id int auto_increment primary key,
    company_name varchar(100) not null,
    create_time timestamp default current_timestamp,
    update_time timestamp
);

create table manager_company(
	id int auto_increment primary key,
    manager_id int not null,
    company_id int not null,
    foreign key(manager_id)
    references manager(id)
    on update cascade
    on delete cascade,
    foreign key(company_id)
    references company(id)
    on update cascade
    on delete cascade
);

create table manager_kind(
	id int auto_increment primary key,
    manager_id int not null,
    kind_id int not null,
    foreign key(manager_id)
    references manager(id)
    on update cascade
    on delete cascade,
    foreign key(kind_id)
    references kind(id)
    on update cascade
    on delete cascade
);

• on update cascade, on delete cascade -> 참조하고 있는 다른 테이블의 컬럼들도 같이 업데이트나 삭제를 하겠다는 제약조건

2. 데이터 CRUD

• 업무 코드 삽입

SELECT id FROM task ORDER BY id DESC LIMIT 1

-> 마지막 id 조회

update task set task_code = 
(select concat('W', '-', LPAD({id},5,'0') )) where id = {id};

-> 마지막 id + 1 을 id에 넣어주어 자동으로 업무 코드를 생성함
ex) 마지막 ID가 2일 때, 새로 삽입하는 업무의 업무 코드 -> W-00003

• RNR 데이터 조회

select * from manager m, task t where m.task_id = t.id;

-> 담당자 테이블과 담당자가 참조하고 있는 업무 테이블을 같이 조회하여 RNR 데이터를 가져온다.