데이터베이스 설계

1. 데이터베이스 설계

데이터베이스를 설계한다는 것은 데이터베이스의 스키마 내에 태이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것을 말한다.

스카마 내에 정의한다는 뜻에서 ‘스키마 설계’라 불리기도 한다.

논리명과 물리명

테이블을 설계할 때는 테이블 정의서나 설계도 등의 문서를 작성하는 경우가 많다.

DESC명령에 따라 표시되는 결과를 그대로 옮겨적은 것이라 봐도 무방하다.

하나의 테이블에 대해 두 개의 이름을 지정할 때도 있는데 하나는 데이터베이스에서 사용될 이름으로 실제로는 CREATE TABLE에 지정하는 이름을 말하며 ‘물리명’이라 부른다. 또 하나는 ‘논리명’이라는 것으로 테이블의 ‘설계상 이름’에 해당된다.

물리명은 데이터베이스 시스템 규칙에 따라 길이에 제한이 있거나 공백문자를 사용할 수 없는 등의 제약이 있다. 예를 들명 테이블의 물리명은 ‘item_master’. 논리명은 ‘상품 마스터’로 지정하는 경우가 있다.

물리명만으로는 의미가 전달되지 않는 경우도 많아 논리명이 필요해진다. 물리명은 잘못 정하면 병경하기 힘들지만 논리명은 언제나 바꿀 수 있다.

⚠️ 물리명 vs 논리명

물리명

• CREATE TABLE명령으로 테이블을 작성할 때 사용하는 이름

논리명

• 해당 테이블을 실제로 부를 때 사용하는 이름

이러한 이유로 물리명과 논리명이 함께 기재된 설계도나 정의서도 있다. 경우에 따라서는 별도의 논리명 기입항목을 지정하지 않고 비고란에 기재하는 경우도 있다.

자료형

금액이나 개수처럼 수치 데이터만 다룰 수 있는 열은 주저없이 수치 자료형으로 지정하면 된다. 한편 제조번호처럼 알파벳도 다루어야 한다면 문자열형으로 지정하는 편이 낫다. 수치형을 문열형으로 변환하는 것은 문제가 되지 않지만 문자열형을 수치형으로 변환하는 경우에는 에러가 발생하기도 해 번거로운 수 있다.

데이터에 따라서는 ‘1, 2, 3 중에 하나’라든가 ‘yes, no중에 하나’만 데이터 값으로 취급하는 경우가 생기기도 한다. 이런경우에는 데이터베이스 기능으로 제약(CHECK제약)을 걸 수 있으므로 데이터 정합성이 중요한 부분에는 적극적으로 사용할 필요가 있다.

데이터베이스 시스템에서 데이터 정합성을 체크할 수 있다면 데이터베이스에 맡겨버리는 편이 가장 확실하고 편리하다. 애플리케이션에서 따로 구현해 사용할 수도 있지만, 이런 경우 정합성이 맞지 않을 수 있으며 개발비용도 상승해 추천하고 싶지 않다.

한편, 앞에서 예로 든 ‘1, 2, 3’등의 경우 각 숫자에 ‘상, 중, 하’와 같은 의미를 부여해 데이터를 사용할 때는 정의서의 비고란에 적어두는 경우가 많다. 데이터의 의미를 따로 정의할 수 없기 때문에 비고란도 중요한 역할을 한다. 다만, MySQL에서는 테이블을 작성할 때 comment라는 키워드를 이용하여 주석을 열 단위로 기입해 둘 수 있다.

고정길이와 가변길이

제조번호처럼 자리수가 이미 정해져 있는 경우에는 고정길이 문자열로 지정하는 편이 좋다. 데이터의 최대 길이 역시 제조번호의 자릴수에 맞춰 정하면 된다. 한편, 비고란과 같이 자주 입력되지도 않지만 입력되는 문자열의 길이의 변동폭이 클 경우 가변결이 문자열이 적합하다.

VARCHAR형으로 지정할 수 있는 최대 크기는 기껏해야 수천 바이트이다. 조금 큰 파일에는 LOB형을 사용한다. 여기서 LOB은 ‘Large Object’의 약자이다 LOB형은 큰 데이터를 다루는 자료형이지만 인덱스를 지정할 수 없다는 제약이 있다.

기본키

기본키로 지정할 열이 생각나지 않는 경우도 많을 것이다. 이러한 경우 자동증가 열을 사용해서 기본키로 지정하면 간단하게 해결할 수 있다.

MySQL의 경우 열을 정의할 때 AUTO_INCREMENT를 지정하는 것으로 자동증가 열이 된다. AUTO_INCREMENT로 지정한 열은 PRIMARY KEY또는 UNIQUE로 유일성을 지정해야 한다. 아무 열이나 자동증가 열로 지정하지 않도록 주의해야 한다.

2. ER다이어그램

테이블 간의 관계를 명확히 하기 위해 설계도를 작성하는 경우가 있다. ER다이어그램은 이런 경우에 널리 쓰이는 도식이다.

E는 개체를 뜻하는 ‘Entity’의 약자이며 R은 ‘Relationship’의 약자이다 즉, ER다이어그램은 개체 간의 관계를 표현한 것이다. ER다이어그램의 관계는 관계형 데이터베이스의 릴레이션과 달리 릴레이션십, 즉 연계를 가리킨다.

엔티티, 즉 개체는 테이블 또는 뷰를 말한다.

개체와 개체가 서로 연계되는 경우에는 선으로 이어서 표현한다.

연계를 표기할 때는 서로 몇 개의 데이터 행과 연관되는지, 즉 몇 대 몇의 관계를 가지는지를 숫자나 기호로 나타낼 수 있다. (카디널리티 또는 다중도라고 한다.) 여러가지 패턴의 표기방법 중에서도 가장 기본적인 세 가지는 다음과 같다.

• 1 : 1
• 1 : M
• M : M

ER다이어그램의 연계는 데이터베이스에서는 외부참조제약 (외부키 제약)으로 지정되는 경우가 있다. 외부키 제약을 설정하면 데이터의 정합성이 업격히 관리되어 번거로워진다는 이유로 이를 채용하지 않는 시스템도 있다. ‘설계상 이렇게 연관되어 있다’라고 나타내는 것이 ER다이어그램의 역할이다.

정규화

정규화란 데이터베이스의 테이블을 규정된 올바른 현태로 개선해나가는 것이다. 정규화는 데이터베이스의 설계 단계에서 행해진다. 경우에 따라서는 기존 시스템을 재검토할 때 정규화하는 경우도 있다.

1. 정규화

위 테이블에서 주문상품 부분이 특별한 형식 없이 대충 만들어진 것처럼 보여 문제가 될 수 있다. 따라서 상품은 상품코드를 이용해 다루기로 하자.

‘0001 OO 1개’ 라고 되어 있는 부분은 상품코드가 0001인 상품 OO를 1개 주문 했다는 뜻이다. 물론 한번 주문할 때 여러개의 상품을 주문할 수도 있다. 주문번호 1의 데이터를 살펴보면 ‘0001 OO이 1개, 0002 XX가 10개’로 두가지 종류의 상품을 주문했다는 것을 알 수 있다. 이렇게 대충 만들어진 데이터를 정규화해 데이터베이스의 테이블로 만들어 보자.

정규화는 단계적으로 실시한다. 그 첫 번째 단계가 제1 정규화로 이를 시행하면 제1 정규형 테이블을 만들 수 있다.

2. 제1 정규형

관계형 데이터베이스의 테이블에는 하나의 셀에 하나의 값만 저장할 수 있다는 제약이 있다. 이로 인해 주문상품의 데이터를 그대로 테이블로 만들 수 는 없다. 적어도 상품코드와 상품명, 개수 데이터를 담는 세 개의 열로 나누어야 한다.

주문상품 데이터를 상품코드와 개수로 분할함에 따라 열이 두 개 더 추가되었고 행도 늘어났다. 이렇게 하나의 셀에 함나의 값만 저장할 수 있도록 하고, 반복되는 부분을 세로(행) 방향으로 늘려나가는 것이 제1 정규화의 제1 단계이다.

💡반복되는 데이터를 가로(열 방향)가 아닌 세로(행 방향)로 늘리는 것이 제1 정규화의 제1 단계이다.

제 1정규화에서는 중복을 제거하는 테이블의 분할도 이루어진다. 예를 들어 한번의 주문으로 여러 개의 상품을 주문할 수 잇으므로 주문번호, 날짜, 성명, 연락처가 동일한 값을 가지는 행이 여러개 존재할 수 있다. 이때 동일한 값을 가지는 행이 여러 개 존재하지 않도록 하나로 정리한다.

먼저, 주문 테이블을 주문상품 테이블과 주문 테이블로 나눈다. 주문 테이블은 주문번호, 날짜, 성명, 연락처로 구성한다. 한편, 주문 상품 테이블은 상품코드, 상품명, 개수로 구성하되, 추가적으로 주문 테이블과 결합할 수 있도록 주문번호 열을 추가한다.

여기에서 분할 이후의 주문 테이블을 자세히 살펴보면, 주문번호에는 중복된 값이 존재하지 않기 때문에 기본키로 지정할 수 있다. 한편, 주문 상품 테이블에서는 주문 번호와 상품코드를 한데 묶어 기본키로 지정할 수 있다.

이처럼 제1 정규화에서는 반복되는 부분을 찾아내서 테이블을 분할하고 기본키가 될 열을 작성할 수 있다.

3. 제2 정규형

제2 정규화에서는 데이터가 중복하는 부분을 찾아내어 테이블로 분할해 나간다. 이때 기본키에 의해 특정되는 열과 그렇지 않은 열로 나누는 것으로 정규화가 이루어진다.

주문상품의 기본키는 주문번호와 상품코드의 두 개 열로 되어있다. 주문번호 1에 상품코드가 0001인 상품 주문량은 총 1개라는 것을 알 수 있다. 이것은 기본키를 바탕으로 특정되는 데이터이다. 즉, 개수 열은 기본키가 결정되고 나면 특정할 수 있는 것이다. 한편, 상품명은 주문번호와 관계없이 상품코드만으로 특정할 수 있다. 상품코드는 기본키의 일부이긴 하지만 단독으로 기본키 역할을 할 수는 없다.

이처럼 두 가지로 분류할 수 있으므로 두 개 테이블로 분할하고 테이블명은 ‘상품’이라 하겠다.

상품 테이블은 상품코드만으로 기본키를 지정했다. 어려운 말로 표현하자면 부분 함수 종석성을 찾아내서 테이블을 분할 하는 것이 제2 정규화이다. 여기서 함수종속성이란 키 값을 이용해 데이터를 특정지을 수 있는 것을 가리킨다.

4. 제3 정규형

마지막으로 제3 정구화이다. 이 또한 중복하는 부분을 찾아내어 테이블을 분할하는 수법이다. 제2 정규화의 경우에는 기본키에 중복이 없는지를 조사했다. 제3 정규화에서는 기본키 이외의 부분에서 중복이 없는지를 조사한다.

분할하기 전의 주문 테이블을 살펴보면 데이터가 중복되어 있다. 같은 사람이 여러번 주문하는 경우가 있기 때문이다. 이때 주문 테이블에서 이름을 기준으로 연락처를 특정지을 수 있다. 단, 주문 테이블의 기본키는 어디까지나 주문번호로, 이름은 기본키와는 관계가 없다. 한편 분할하여 새로 만들 테이블의 이름은 ‘고객’이라 붙였다.

여기서도 이름을 기본키로 지정하면 동명이인의 경우 데이터를 제대로 저장할 수 없으므로 고객번호를 기본키로 지정하여 고객 테이블을 작성했다.

실제로는 제5 정규형까지 있다. 다만, 대부분의 시스템에서 제3 정규형까지의 정규화를 채택한다.

이제 정규화 후 테이블은 살펴보면 다음과 같다.

정규화를 통해 테이블을 분할할 때에는 서로 결합할 수 있도록 기본키를 추가해 분할한다. 그럼 정규화 이후 테이블 간 연관관계를 ERD로 표기하면 다음과 같다.

여기서 주문 상품 테이블과 상품 테이블 간의 연계(릴레이션십)에서는 상품 쪽이 1, 주문상품 쪽이 M으로 1:M이라는 다중도를 나타낸다. 즉, 주문상품 테이블 쪽의 데이터에서 상품 테이블을 보면 상품 테이블의 하나의 행을 특정하는 것이다.

반대로 상품 테이블 쪽에서 주문상품 테이블을 보면 하나의 상품이 여러 번 주문되었기 때문에 상품 코드를 이용해도 주문상품 테이블의 하나의 행만 특정할 수 없다. 다시 말해 여러개의 행이 존재한다는 이야기이다. 그 밖에 주문상품의 상품코드에 (FK)라고 적혀 있는 것은 외부키 속성을 가진다는 의미이다.

5. 정규화의 목적

정규화에서는 중복하거나 반복되는 부분을 찾아내서 테이블을 분할하고 기본키를 작성해 사용하는 것을 기본 개념으로 삼는다. 이는 ‘하나의 데이터는 한 곳에 있어야 한다’는 규칙에 근거한다.

하나의 데이터가 반드시 한 곳에만 저장되어 있다면 데이터를 변경하더라도 한 곳만 변경하는 것으로 끝낼 수 있다. 반면 정규화되지 않은 경우에는 여기저기 중복해서 저장된 데이터를 검색하고 일일이 변경해야 한다. 또한 인덱스가 지정된 열의 데이터가 변경되는 경우에는 인덱스도 재구축해야 한다.

하지만 기본키는 분할한 테이블끼리 연계하기 위해 작성한, 이른바 내부적인 데이터이므로 변경될 일은 거의 없다. 따라서 정규화를 통해 테이블에 대한 인덱스의 재구축을 억제할 수 있다.

트랜잭션

START TRANSACTION
COMMIT
ROLLBACK

1. 트랜잭션

발주 처리

먼저 주문 번호를 지정해야 한다. 이때 기존 주문과 구분되는 주문번호를 발행하는 처리가 필요하다. 주문 번호를 발행 받았다면 해당 번호를 키로 삼아 INSERT가 이루어 진다. 주문 테이블에는 INSERT한 번, 주문 상품 테이블에는 주문된 상품 수만큼 INSERT명령이 실행된다. 중요한 것은 복수의 테이블에 INSERT되므로 실행되는 명령은 최소 두 번이라는 것이다.

INSERT INTO 주문 VALUES(4, '2014-03-01', 1);
INSERT INTO 주문상품 VALUES(4, '0003', 1);
INSERT INTO 주문상품 VALUES(4, '0004', 2);

여기서 INSERT 명령이 특정 원인으로 인해 에러가 발생한 경우를 가정해보자 트랜잭션 기능을 사용하지 않을 때는 문제없이 실행된 INSERT 명령을 실행 전으로 되돌릴 수 없으므로 따로 DELETE명령을 실행해 지워야 한다. 즉 위 예시에서 세 번째 INSERT명령에서 에러가 발생했다고 치면, 앞서 실행한 두 개의 INSERT 명령에 의해 추가된 데이터를 DELETE 명령으로 삭제하는 처리가 필요하다. 이것은 아주 번거로운 작업이다.

2. 롤백과 커밋

몇 단계로 처리를 나누어 SQL명령을 실행하는 경우 트랜잭션을 자주 사용한다. 트랜잭션을 사용해서 데이터를 추가한다면 에러가 발생해도 트랜잭션을 롤백해서 종료 할 수 있다.

롤백하면 트랙잭션 내에서 행해진 모든 변경사항을 없었던 것으로 할 수 있다. 아무런 에러가 발생하지 않는다면 변경사항을 적용하고 트랜잭션을 종료하는데, 이때 커밋을 사용한다.

자동커밋

트랜잭션을 사용해서 데이터를 추가할 때는 자동커밋을 꺼야 한다. MySQL클라이언트에서 명령을 실행할 때는 자동커밋이 켜져 있는 상태이다. INSERT나 UPDATE, DELETE가 처리될 때마다 트랜잭션은 암묵적으로 자동커밋 상태로 되어 있다. 자동커밋을 끄기 위해서는 명시적으로 트랜잭션의 시작을 선언할 필요가 있다.

# 트랜잭션 시작
START TRANSACTION

트랜잭션을 종료하기 위해서는 변경된 내용을 적용한 후에 종료하는 ‘커밋’과 적용하지 않고 종료하는 ‘롤백’의 두 가지 방식이 있다. 커밋할 때에는 COMMIT명령을 사용한다.

# 트랜잭션 내에서 실행한 명령을 적용한 후 종료
COMMIT

롤백은 ROLLBACK명령을 사용한다.

# 트랜잭션 내에서 실행한 명령을 파기한 후 종료
ROLLBACK

트랜잭션 내에서 실행된 SQL명령은 임시 데이터 영역에서 수행되다가, COMMIT명령을 내리면 임시 데이터 영역에서 정식 데이터 영역으로 변경이 적용된다고 생각하면 된다. ROLLBACK명령을 내리면 임시 데이터 영역에서의 처리는 버려진다.

다음은 에러가 발생하지 않은 경우의 사례로, 트랜잭션은 COMMIT을 이용해 종료한다.

START TRANSACTION;
INSERT INTO 주문 VALUES(4, '2014-03-01', 1);
INSERT INTO 주문상품 VALUES(4, '0003', 1);
INSERT INTO 주문상품 VALUES(4, '0004', 2);
COMMIT;

이렇게 트랜잭션을 시작해서 SQL명령을 실행하고 COMMIT또는 ROLLBACK명령으로 트랜잭션을 종료하는 일련의 처리방법을 ‘트랜잭션을 걸어서 실행한다’ 또는 ‘트랙잭션 내에서 실행한다’라고 한다.

3. 트랜잭션 사용법

하나의 명령에 트랜잭션을 걸어 실행하는 것은 별로 의미가 없다.

트랜잭션 내에서 실행하는 복수의 SQL 명령은 세트 단위로 유효/무효가 된다. 다시 말하면, 반드시 세트로 실행하고 싶은 SQL명령을 트랜잭션에서 하나로 묶어 실행한다는 것이다. 또 ROLLBACK은 에러가 발생한 경우 변경사항이 적용되지 않도록 하는 목적으로 주로 사용한다.

다만, 에러가 발생하지 않아도 ROLLBACK을 하면 변경한 내용은 파기된다. 반대로 에러가 발생하더라고 COMMIT을 하면 문제없이 실행된 SQL 명령의 변경사항은 데이터베이스에 그대로 반영된다.

트랜잭션을 시작할 때 사용하는 명령은 START TRANSACTION이나 MySQL에서는 이외에도 BEGIN을 사용할 수 있다. SQL Server나 PostgreSQL에서는 BEGIN TRANSACTION명령을 사용한다. Oracle이나 DB2에서는 트랜잭션을 시작하는 명령은 따로 없다.

자동커밋은 클라이언트 툴의 기능이다. 미들웨어도 데이터베이스 접속 시 대게 자동커밋을 한다. 한편, 데이터베이스 서버에서는 언제나 트랜잭션을 걸 수 있는 상태로 SQL 명령이 실행된다.

DELETE 명령은 삭제 여부에 관해 사용자에게 확인하지 않는다. 하지만 DELETE명령을 트랜잭션 내에서 실행하는 경우에는 ROLLBACK으로 삭제를 취소할 수 있다. 단, 자동커밋으로 되어있는 경우에는 주의해야한다. ROLLBACK으로 취소할 수 있는 것은 트랜잭션 내에서 실행했을 경우에 한한다.

Question

CREATE TABLE명령에서 지정하는 명칭

• 물리명

ERD에서 네모난 상자로 표시되는 개체?

• 테이블

트랜잭션 내에서 실행한 명령을 파기하고 종료하는 명령

• ROLLBACK