[번역] 데이터 구조와 설계 — 튜토리얼

본 글은 Database Structure and Design Tutorial by Lucidchart의 번역 글입니다.

Lucidchart 와 같은 신뢰할 만한 데이터베이스 설계 도구를 사용하면, 잘 설계된 데이터를 통해 사용자가 필수 정보에 접근할 수 있습니다. 본 튜토리얼에서 설명하는 원칙을 따르면, 잘 동작하면서 앞으로 발생할 수 있는 요청을 반영할 수 있도록 데이터베이스를 설계할 수 있습니다. 데이터베이스를 구성하는 방법뿐만 아니라 최적화된 결과를 위해 정제하는 방법을 살펴보도록 하겠습니다.

데이터베이스 설계 프로세스

잘 설계된 데이터베이스는

• 불필요한 데이터를 제거함으로써 디스크 공간을 절약합니다.
• 데이터 정확성(accuracy)과 무결성(integrity)을 유지합니다.
• 유용한 방법으로 데이터에 접근할 방법을 제공합니다.

효율적이면서 유용한 데이터베이스를 설계하는 것은, 다음 단계와 같은 적절한 프로세스를 따라가는지 아닌지와 관계가 있습니다.

1. 요구사항을 분석하거나 데이터베이스의 용도 식별하기
2. 데이터를 테이블로 구성하기
3. primary key를 찾아내고 관계를 분석하기
4. 테이블을 standardize 하기 위해 normalizing 하기

각 단계를 자세히 알아가 보도록 하죠. 참고로 이 가이드는 Edgar Codd의 관계형 데이터베이스 모델을 SQL(계층적, 네트워크, 또는 객체 데이터 모델이 아닌)로 작성한 것을 다룹니다. 데이터베이스 모델에 대한 자세한 내용은 여기에 있는 가이드를 참고하세요.

요구사항 분석: 데이터베이스의 용도 식별하기

여러분의 데이터베이스의 목적을 이해하게 되면 설계 과정 전반에서 어떤 방향으로 나아가야 할 지 알 수 있습니다. 데이터베이스를 다양한 관점에서 고려해야 하죠. 예를 들어, 공공 도서관에 필요한 데이터베이스를 만든다고 했을 때, 사서와 이용객 모두가 데이터에 접근할 필요가 있다는 것을 생각해야 합니다.

데이터베이스를 만들기 전에, 다음 방법을 통해서 정보를 얻을 수 있습니다.

• 데이터베이스를 사용할 사람들을 대상으로 인터뷰하기
• 명세서, 시간 기록표, 설문 결과와 같은 문서 분석하기
• 기존에 존재하는 데이터 시스템 찾아보기(물리적, 디지털 형식을 포함하여)

이제 데이터베이스에 포함하고자 하는 존재하는 데이터를 모으기 시작하세요. 그런 다음 저장하려는 데이터 유형과 해당 데이터가 설명하는 개체나 사람, 사물, 위치 및 이벤트를 다음과 같이 나열합니다.

고객(Customer)

• 이름(Name)
• 주소(Address)
• 도시, 주, 우편번호(City, State, Zip)
• 이메일(Email address)

제품(Products)

• 이름(Name)
• 가격(Price)
• 재고 수량(Quantity in stock)
• 주문 수량(Quantity on order)

주문(Orders)

• 주문 ID(Order ID)
• 영업 담당자(Sales representative)
• 날짜(Date)
• 제품(Product(s))
• 수량(Quantity)
• 가격(Price)
• 총계(Total)

이 정보는 나중에 데이터베이스 내의 테이블과 필드의 윤곽을 그릴 데이터 딕셔너리(data dictionary)의 일부분이 될 것입니다. 이때 정보를 최대한 작지만 유용한 단위로 쪼개야 합니다. 예를 들어, 주소 정보에서 도로명과 국가 정보를 분리하는 것을 고려해 놓으면, 국가별로 사람을 조회할 수 있게 됩니다. 또, 같은 데이터 요소를 두 개 이상의 테이블에 저장하는 것을 피해야 합니다. 이로 인해 불필요한 복잡성이 증대되기 때문이죠.

데이터베이스에 어떤 종류의 데이터를 포함하고, 데이터의 출처와 그 데이터가 어떻게 활용될지 알게 되면, 실제 데이터베이스를 계획할 준비가 된 것입니다.

데이터베이스 구조: 데이터베이스의 구성 요소(building blocks)

다음 단계는 여러분의 데이터베이스를 시각적으로 나열하는 것입니다. 이를 위해서 여러분은 관계형 데이터베이스가 어떻게 구성되는지 정확히 이해하셔야 합니다.

데이터베이스 내부에서는, 서로 관련된 데이터는 테이블로 묶여 있고, 각 테이블은 마치 스프레드시트처럼 행(row)과 열(column)로 구성되어있습니다.

데이터 목록을 테이블로 변형하기 위해서는, 각 개체의 종류별로 테이블을 만들기 시작하면 됩니다. 제품(products), 매출(sales), 고객(customers) 그리고 주문(orders)과 같이 말이죠. 다음 예제를 살펴보도록 합시다.

테이블의 각 행(row)을 레코드(record)라고 합니다. 레코드는 어떤 것 또는 어떤 사람의 정보를 담고 있습니다. ‘어떤 고객의 정보’와 같은 특정 대상처럼요. 반면, 컬럼(column, 필드(field) 또는 속성(attribute)이라고 불리기도 합니다)은 각 레코드에 표시되는 단일 유형의 정보들을 포함하고 있습니다. 마치 테이블에서 ‘모든 고객의 주소’와 같이 말이죠.

레코드들이 일관된 데이터를 저장하게 하기 위해서는 각 컬럼별로 적합한 데이터 유형을 지정해야 합니다. 일반적으로 다음과 같은 데이터 유형이 있습니다.

• CHAR —고정된 길이의 문자
• VARCHAR — 가변적인 길이의 문자
• TEXT — 많은 양의 문자
• INT — 음수이거나 양수인 모든 숫자
• FLOAT, DOUBLE —소수점을 가질 수 있는 숫자
• BLOB — 바이너리 데이터

몇몇 데이터베이스 관리 시스템은 자동숫자(Autonumber) 데이터 유형을 제공하기도 합니다. 이 데이터 유형은 행마다 고유한 숫자를 자동으로 생성해줍니다.

개체-관계 다이어그램(entity-relationship diagram)이라는 데이터베이스를 시각적 개요를 그릴 때는 실제 테이블을 포함하지 않습니다. 대신, 테이블 하나가 한 개의 박스로 표현되죠. 각 박스의 제목은 해당 테이블이 어떤 데이터를 다루는지 나타내고, 그 아래에 속성들이 다음과 같이 표시됩니다.

마지막으로, 테이블별로 어떤 속성이 프라이머리 키(primary key)가 될지 (필요한 경우) 정해야 합니다. 프라이머리 키(Primary key, PK)란, 각 개체의 고유 인식자(unique identifier)입니다. 즉, 이 값만 알아도 특정 고객을 선택해낼 수 있다는 의미입니다.

프라이머리 키로 선택된 속성은 고유하고, 변경되지 않고, 언제나 존재하는(절대 빈값이나 NULL이 아닌) 상태여야 합니다. 이러한 이유로, 주문 번호나 유저 이름은 프라이머리 키로 사용하기 적합하고, 전화번호나 도로 주소의 경우에는 그렇지 않습니다. 또한, 여러 가지 필드를 함께 사용하여 프라이머리 키를 만들어낼 수 있습니다(이런 키를 복합키(composite key)라고 부릅니다).

실제 데이터베이스를 만들 때가 되면, 여러분은 논리적인 데이터 구조와 물리적인 데이터 구조를 데이터 관리 시스템이 지원하는 데이터 정의 언어로 표현해야 합니다. 이 시점에서 데이터베이스의 크기를 예측해야 데이터베이스가 요구하는 성능 수준과 저장 공간을 확보할 수 있습니다.

개체 간 관계 생성하기

여러분의 데이터베이스 테이블이 여러 테이블로 변환되고 나면, 이제 테이블간의 관계를 분석할 수 있게 됩니다. 카디널리티(Cardinaliry)란 두 개의 관련된 테이블 간에 상호작용하는 인자의 개수를 의미합니다. 카디널리티를 인식하게 되면 데이터를 가장 효율적으로 테이블에 나눌 수 있게 됩니다.

각 개체는 다른 모든 개체와 관계를 맺을 수 있죠. 하지만 그 관계는 보통 다음의 세 가지 유형 중의 하나입니다.

일대일 관계(One-to-one relationships)

만약 개체 B의 각 인스턴스에 대해 하나의 개체 A의 인스턴스가 존재할 때 이 둘은 일대일 관계라고 합니다(종종 1:1이라고 표시됩니다). 이와 같은 관계를 ER 다이어그램(entity relationship diagram)에서는 각 끝의 대시 기호를 그린 선분을 통해서 나타낼 수 있습니다.

반드시 1:1 관계로 테이블을 구성해야 할 필요가 없는 한, 1:1 관계는 대개 두 테이블의 데이터를 단일 테이블로 통합하는 것이 더 낫다는 것을 의미합니다.

만약 “설명"이라는 어떤 선택적인 데이터를 저장하는 필드가 있고, 이 값은 많은 레코드에서 빈값일 때, “설명" 필드를 전부 제거하고 새로운 테이블에 저장할 수 있습니다. 그러면 빈 공간을 줄이고 데이터베이스 성능을 증대시킬 수 있죠.

데이터가 정확히 일치하는지 보장하기 위해서, 각 테이블에 최소한 한 개 이상에 같은 컬럼을 가지고 있어야 합니다. 이때 주로 프라이머리 키를 사용하죠.

일대다 관계(One-to-many relationships)

이와 같은 관계는 한 테이블의 레코드가 다수의 항목과 관련되었을 때 발생합니다. 예를 들어, 한 명의 고객은 여러 개의 주문을 생성할 수 있고, 도서관 고객은 한 번에 여러 권의 도서를 대여할 수 있죠. 일대다(1:M) 관계는 다음의 예시와 같이 “까마귀 발 표기법(Crow’s foot notation)”이라는 표기법으로 표시됩니다.

데이터베이스를 구성할 때 1:M 관계를 구현하려면, 한쪽의 프라이머리 키를 다른 쪽의 속성으로 추가하기면 하면 됩니다. 이 방식으로 다른 테이블에 프라이머리 키가 나열될 때, 이 프라이머리 키를 외래키(foreign key)라고 부릅니다. 1:M에서 “1” 방면의 테이블이 상위 테이블(parent table)이라 부르며, 다른 방면의 테이블을 하위 테이블(child table)이라고 합니다.

다대다 관계(Many-to-many relationships)

한 테이블의 복수의 개체가 다른 테이블의 복수의 개체와 관련이 있을 때, 이 둘은 다대다 (M:N) 관계가 있다고 합니다. 이러한 관계는 학생과 수업 간의 경우에서 발생할 수 있는데요, 학생 한 명은 여러 수업을 수강할 수 있고, 한 수업은 여러 명의 학생으로 구성되기 때문이죠.

이를 ER 다이어그램에서 다음과 같은 선분을 사용하여 표현할 수 있습니다.

안타깝게도, 이런 관계는 데이터베이스에서 직접 구현할 수가 없습니다. 대신, 이러한 관계를 두 개의 일대다 관계로 분리해야 합니다.

이를 위해선, 두 테이블 사이에 새로운 개체를 생성합니다. 만약 M:N 관계가 판매(sales)와 제품(products) 간에 존재한다면, 새로운 개체는 ‘판매된 제품(sold_products)’으로 부르는 것이 적합할 겁니다. 각 판매별 제품을 표시할 것이기 때문이죠. 그러면 판매와 제품 테이블은 각각 ‘판매된 제품' 테이블과 1:M 관계를 맺게 될 것입니다. 이런 종류의 중개역할을 하는 개체를 모델에 따라 연결 테이블(link table), 결합 개체(associative entity) 또는 교차 테이블(junction table)이라고 부릅니다.

연결 테이블의 각 레코드는 인접한 테이블의 개체 중 두 개를 연결합니다(이 레코드는 부가적인 정보도 포함하게 되죠). 예를 들어, 학생과 수업은 이런 모습을 보일 겁니다.

필수(mandatory)인가 아닌가?

관계를 분석하는 또 다른 방법은 관계에서 한 방면이 존재하기 위해 어떤 방면이 반드시 존재해야 하는지 생각해보는 것입니다. 필수적이지 않은 방면은 선분에서 대쉬 기호 옆에 동그라미를 표시합니다. 예를 들어, UN에서 대표를 하기 위해서는 국가가 존재해야 하지만, 그 반대는 그렇지 않습니다.

두 개체는 서로 의존적이게 됩니다(한 방면은 다른 한 방면 없이 존재할 수 없습니다).

재귀적 관계(recursive relationships)

종종 테이블이 자신을 다시 가리키게 되는 경우가 있습니다. 예를 들어, 직원(employees) 테이블은 “매니저(manager)”라는 속성으로 해당 테이블 내의 한 개인을 가리킬 수 있습니다. 이것을 재귀적 관계(recursive relationships)라고 부르죠.

중복 관계(redundant relationships)

중복 관계(redundant relationships)란 두 번 이상 표현되는 것을 말합니다. 일반적으로는, 중요한 정보를 잃지 않으면서 관계를 제거할 수 있습니다. 예를 들어, “학생(students)” 개체가 “선생님(teachers)” 개체와 직접적인 관계가 있지만 “수업(classses)” 개체를 통해 선생님 개체와 간접적인 관계가 존재할 때 학생과 선생님의 관계를 제거하는 것이 좋습니다. 왜냐하면, 학생들이 선생님에게 할당되는 유일한 방법은 수업을 통해서 만이기 때문이죠.

데이터베이스 정규화(Database normalization)

데이터베이스의 기초적인 설계를 마쳤다면, 테이블이 정확하게 구조화될 수 있도록 정규화(normalization) 규칙을 적용할 수 있습니다. 이 규칙은 마치 산업 표준이라고 생각하시면 됩니다.

그렇지만, 모든 데이터베이스가 정규화를 하기에 적합한 대상이 아닙니다. 보통, 온라인 거래 프로세싱(online transaction processing, OLTP) 데이터베이스는 사용자들이 거래 내용을 생성하고, 읽고, 업데이트하고 삭제하는 것에 관심이 있기 때문에 정규화되어야 합니다.

온라인 분석 프로세싱(online analytical processing, OLAP) 데이터베이스는 분석과 리포팅을 위해 사용되기 때문에 어느 정도의 비정규화가 더 나을 수 있습니다. 왜냐하면, 연산의 속도가 강조되기 때문이죠. 여기에는 데이터가 변경되지 않고 신속하게 분석해야 하는 의사 결정 지원 애플리케이션이 포함됩니다.

각 형(form), 또는 정규화 단계는 하위 단계 형의 규칙을 포함합니다.

제1 정규형(First normal form)

제1정규형(1NF)은 테이블의 각 셀은 여러 값의 리스트가 아닌 오직 한 개의 값만을 가질 수 있다고 정의합니다. 따라서 다음과 같은 테이블은 규칙에 어긋난 경우입니다.

여러분은 아마 추가 컬럼을 생성하고 그곳에 데이터를 옮김으로써 이 문제를 해결하고 싶은 충동이 드시겠지만, 그것 또한 규칙에 어긋납니다. 반복되거나 밀접하게 관계가 있는 속성들의 묶음이 있는 테이블은 제1 정규형 규칙에 부합하지 않습니다. 예를 들어 아래의 테이블 또한 규칙에 어긋난 경우입니다.

대신, 각 셀이 한 개의 데이터를 가지고 추가 컬럼이 존재하지 않을 때 까지 데이터를 여러 개의 테이블이나 레코드로 분리하세요. 이때, 데이터는 원자성을 가지고 있다(atomic)고 합니다. 또는 가장 작고 유용한 크기로 분리되었다고 합니다. 위의 테이블을 살펴보았을 때, “판매 상세(Sales details)"라는 추가 테이블을 생성해서 특정 상품과 판매를 연결할 수 있습니다. “판매(Sales)”는 “판매 상세(Sales detail)”와 1:M 관계를 맺게 되겠죠.

제2 정규형(Second normal form)

제2정규형(2NF)은 각 속성이 모든 프라이머리 키에 완전히 종속적이어야 한다고 규정합니다. 즉, 각 속성은 다른 속성을 통해 간접적으로 의존적인 것이 아니라 프라이머리 키에 직접 의존적이어야 합니다.

예를 들어, “나이(age)" 속성은 “생일(birthdate)”에 의존적이며 이는 결국 “학생ID(studentID)”에 의존적일 때, “나이" 속성은 부분적으로 기능 의존성을 가지고 있다고 하며, 이런 속성을 포함하고 있는 테이블은 제2 정규형에 부합하지 않게 되죠.

게다가, 여러 필드로 구성된 프라이머리 키를 사용하는 테이블은, 한 개 이상의 필드가 키의 모든 부분에 의존적이지 않으면, 제2 정규형을 위반하게 됩니다.

따라서, 이런 필드를 가진 테이블은 제2 정규형에 부합하지 않습니다. 왜냐하면, “제품 명(product name)” 속성은 제품 ID(product ID)에는 의존적이지만 주문 번호(order number)에는 의존적이지 않기 때문이죠.

• 주문 번호(Order number, 프라이머리 키)
• 제품 ID(product ID, 프라이머리 키)
• 제품 명(product name)

제3 정규형(Third normal form)

제3 정규형(3NF)은 키가 아닌 모든 컬럼은 다른 컬럼에 대해서 독립적이어야 한다는 규칙을 추가합니다. 만약 하나의 키가 아닌 컬럼의 값을 변경할 때, 다른 컬럼의 값이 변한다면 이 테이블은 제3 정규형에 부합하지 않습니다.

이 규칙은 테이블에 파생된 데이터를 저장하지 못하도록 합니다. 마치 다음 테이블에서 가격에 의존적인 “세금(tax)” 컬럼과 같이 말이죠.

이외에도 Boyce-Codd 정규형, 제4 부터 6 정규형, 그리고 도메인-키(domain-key) 정규형과 같이 추가적인 정규형이 제안되었지만, 위의 세 가지 정규형이 가장 많이 사용되고 있습니다.

이런 정규형들은 일반적으로 따를 만한 좋은 관습들을 설명하고 있지만, 정규형의 정도는 데이터베이스에 따라 다릅니다.

다차원 데이터(Multidimensional data)

어떤 사람들은 단일 데이터 유형의 복수의 차원에 접근하고자 합니다. 특히 OLAP 데이터베이스에서 말이죠. 예를 들어, 고객 당 판매량(sales by customer), 현재 상태(state) 그리고 판매 월(month)을 알고자 한다고 합시다. 이런 상황에서는, 중앙 테이블(central fact table)을 만들어 다른 고객(customer), 상태(state), 그리고 판매 월(month) 테이블이 참조할 수 있도록 합니다. 다음과 같이 말이죠.

데이터 무결성 규칙

데이터베이스의 포함될 데이터가 적절한 규칙에 따라 유효성을 검사하도록 설정해야 합니다. 많은 데이터베이스 시스템들, 마이크로소프트 액세스(Microsoft Access)와 같은 시스템은 이런 규칙을 자동으로 적용하기도 하죠.

개체 무결성 규칙(entity integrity rule)에 따르면, 프라이머리 키는 절대 NULL 값을 가지고 있으면 안 됩니다. 만약 키가 여러 컬럼으로 구성되어 있다면, 구성된 컬럼들은 NULL 값을 가지면 안 됩니다. 그렇지 않으면, 레코드를 고유하게 인식할 수 없게 됩니다.

참조 무결성 규칙(referential integrity rule)은 한 테이블에 나열된 외래키가 참조하는 테이블의 하나의 프라이머리 키와 일치해야 함을 요구합니다. 만약 프라이머리 키가 변하거나 삭제되면, 이러한 변화는 데이터베이스 내외에서 그 키를 참조하는 모든 것에 적용되어야 합니다.

비즈니스 논리 무결성 규칙(business logic integrity)은 데이터가 특정 논리 매개 변수에 맞는지 확인합니다. 예를 들어, 예약 시간은 영업시간에 포함되도록 해야 할 테죠.

인덱스(index)와 뷰(view) 추가하기

인덱스(index)란, 한 개 또는 그 이상의 컬럼을 정렬하여 복사한 것입니다. 이때 값을 오름차순 또는 내림차순으로 정렬하죠. 인덱스를 추가하게 되면 유저는 레코드를 훨씬 빠르게 찾을 수 있습니다. 각 쿼리를 재정렬 하는 대신에, 시스템은 인덱스에 순서대로 정의된 레코드에 접근할 수 있습니다.

물론 인덱스가 데이터 조회를 빠르게 하지만, 데이터를 삽입, 업데이트 그리고 삭제를 느리게 할 수 있습니다. 왜냐면 레코드가 변화할 때마다 인덱스를 새로 구성해야 하기 때문이죠.

뷰(view)란, 단순히 데이터에 저장된 쿼리를 의미합니다. 뷰는 여러 테이블의 데이터를 유용하게 결합(join)하거나 테이블 일부분을 보여줍니다.

확장된 속성

기본적인 윤곽이 완성되면, 데이터베이스를 확장 속성을 사용하여 데이터베이스를 정제할 수 있습니다. instructional text, input masks, 그리고 formatting rules와 같은 것들을 특정 스키마, 뷰 또는 컬럼에 적용할 수 있습니다. 해당 방식의 장점은, 규칙들이 데이터베이스 그 자체에 저장되기 때문에, 데이터에 접근하는 다양한 프로그램들에서 데이터가 일관되게 나타납니다.

SQL과 UML

통합 모델링 언어(Unified Modeling Language, UML)는 객체 지향 프로그램 언어로 만든 복잡한 시스템을 시각적으로 표현하는 또 다른 방법입니다. 본 가이드에서 설명한 여러 개념은 UML에서는 다른 이름으로 알려져 있습니다. 예를 들어, 개체는 UML에서는 클래스(class)라고 알려져있습니다.

요즘 UML은 예전만큼 자주 쓰이지는 않습니다. 오늘날에는 학문적으로 활용되거나 소프트웨어 설계자들과 클라이언트들의 소통 도구로 종종 사용됩니다.

데이터베이스 관리 시스템

설계 선택의 대부분은 어떤 데이터베이스 관리 시스템을 쓰느냐에 따라 달라질 것입니다. 몇몇 자주 사용되는 시스템은 다음과 같습니다.

• 오라클 DB
• MySQL
• 마이크로소프트 SQL 서버
• PostgreSQL
• IBM DB2

선택권이 주어졌을 때는 가격, 운영체제 기능 등을 기반으로 데이터베이스 관리 시스템을 선택하시면 됩니다.