중학교 학생들의 성적을 관리하는 시스템의 DB를 설계 해보자!
성적 관리 요구 사항
- 중학교(남녀공학) 학생 성적관리 프로젝트
- 과목은 학년별로 담당 선생님이 따로 있다.
- 시험은 중간고사, 기말고사 두 가지가 있다.
- 학생들은 학년, 반에 배정되며 반별로 학생들에게 고유 번호를 부여하고 있다.
- 각 반에는 담임 선생님이 배정되어 있다. 선생님 중에는 담임을 맡지 않는 선생님도 있다.
- 한 반의 학생은 대략 40명 정도이고 남녀 공학이다.
- 석차는 남녀 공통 1등부터 순서대로 정한다.
마스터 테이블
- 학생(반당 40명정도, 반별 고유 번호 존재), 학년, 반
- 과목(학년별 담당 선생님 별도로 존재), 선생님(담임/비담임)
- 성적(반 석차/ 학년 석차) ⇒ 시험에 의한 객체 생성
- 시험(중간고사, 기말고사)
관계 테이블
- 시험(보다), 배정(하다), 개강(하다)
만약 실무에서 시나리오에 모르는 단어가 있는 경우, 고객에게 그 개념을 물어봐서 확실히 개념을 잡고 설계한다.
논리적 설계
온전한 명사 선별하기
가장 먼저 해야할 것은 마스터 테이블로 존재할 수 있는 객체들을 선별하는 것이다.
종속성, 수식어 등을 파악하여 마스터 테이블의 객체를 정리해나간다. 예를 들어, 학생과 고유 번호가 있을 때 고유 번호는 학생에 종속되므로 학생 객체의 속성으로 들어가는 것이 맞다. 또한, 담임과 선생님을 생각해보면, 담임은 선생님의 수식어이기 때문에 선생님 객체의 속성으로 들어가는 것이 맞다. 이렇게 종속성 또는 수식어 등을 파악하여 명사로 온전히 존재할 수 있는 객체를 가려낼 수 있다. 만약 속성으로 들어갔던 컬럼이 알고보니 복잡도가 높다고 판단되면 마스터 테이블로 분리해내면 된다.
더 이상 분열할 수 없을 만큼 작은 단위로 객체를 마스터 테이블로 만들면 된다. 그렇다고 마스터가 아닌 것을 마스터로 만들면 굉장히 어색해진다.
1:M 관계 찾기
그 다음 해야할 것은 마스터 테이블 간의 1:M(부자지간) 관계가 있는지 살펴보는 것이다.
1:M 관계는 직관적으로 보이기 마련이다.
데이터베이스 스키마 설계는 많은 경험치가 필요한 일이다. 처음부터 잘 못하더라도 괜찮다.
기준 테이블
자식의 포지션이 되지 않는 테이블을 절대 마스터 테이블이라고 하고 다른 말로 기준 테이블 이라고 한다.
기준 테이블에서 오류가 발생하면 그 이하 연결된 자식 테이블에 모두 문제가 발생한다. 기준 테이블같은 경우에는 한 번 생성되면 거의 변경이 없는 성격을 가진다. 그렇기 때문에 DBA 입장에서 가장 신경 써야 하는 것은 기준 테이블에 오류가 있느냐를 파악해야 한다. 예제에서 기준 테이블은 학년으로 볼 수 있다.
기준 테이블의 경우 한 번 생성되면 그 이후에는 변경될 일이 거의 없다. 따라서 생성된 이후에는
INSERT,DELETE,UPDATE를 막아버리는 것이 좋다. 즉,SELECT만 열어줘서 read-only 로 설정하는 것이 좋다.
고정 관념 버리기
데이터 타입을 정할 때 주의해야 할 점은 고정 관념에 빠져서 잘못된 데이터 타입을 설정해주는 것이다.
예를 들어, 반이름의 경우 보통 1반, 2반, … 이런 식일텐데, 개나리반, 무궁화반 처럼 이럴수도 있다. 이럴때 varchar의 글자수를 전자에 맞췄을 경우 글자수 부족 문제가 발생할 수도 있다. 따라서, 고정 관념에 빠져서 데이터 타입을 설정하는 것에 주의해야 한다. 이런 것은 확장성을 고려하지 않은 설계 방식이다.
코드 레벨에서 할 일
확장성을 고려한 설계 이후에는 개발 단계에서 요구 사항에 맞도록 검증 로직을 추가하면 된다. 예를 들어, 반의 개수는 최대 12반 까지 있어야 한다면, tinyint로 충분하다. 그리고 코드 레벨에서 1반 부터 12반까지만 입력되도록 검증 로직을 추가하면 된다. 즉, 입력되는 값에 대한 컨트롤은 설계 단계에서 할 필요가 없다.
ERD 그리기
- 반ID가 학년별로 1반, 2반 … 체계를 갖기 위해 상속형 PK를 사용했다.
앞서 말했듯이 학년 기준 테이블은 거의 변경될 일이 없으므로 미리 값을 넣어놔서 DML을 사용하지 않도록 한다.
INSERT INTO 학년 VALUES (1, '1학년'), (2, '2학년'), (3, '3학년');
SELECT * FROM 학년;
학년 테이블
| 학년ID | 학년명 | |
|---|---|---|
| 1 | 1 | 1학년 |
| 2 | 2 | 2학년 |
| 3 | 3 | 3학년 |
반면에 반 테이블은 값이 바뀔 수 있는 있기 때문에 DML을 허용해야 한다. 이처럼 비즈니스 상황에 따라 또는 고객의 요구사항에 따라 DML을 허용해야 할 지 파악하고 데이터를 미리 넣을지 말지 결정하면 된다.
학년, 반, 학생 객체
학년, 반, 학생 객체들에 대해 PK와 최소한의 속성으로만 ERD를 구성했다.
PK에 어떤 의미를 부여하는 행동은 지양해야 한다. 확장성과 유연성을 망가뜨리는 행위이다. PK는 유니크하고 낫널이면 충분하다.
- 예약어와 같은 혼란을 빚을만한 단어로는 테이블명으로 사용하지 않는다. (ex. 학급, 반 →
class사용 금지) - 각 테이블명들은 일관성있게 작성하는 것이 좋다. (ex. 학년 → school_grade, 반 → school_class)
- 참고로 작명할 때 테이블은 접두어로
TB, 뷰는VW, 스토어드 프로시저는SP가 붙는 경우도 있다. 이름만 보고 테이블인지 뷰인지 스토어드 프로시저인지 바로 파악하기 편리하다.