[코드잇 SQL 데이터베이스] 데이터베이스 모델링

1. 데이터베이스 모델링이란?

데이터 모델링

데이터를 "어떻게 저장할지"를 정한다.

(1) 개념적 구조를 정하는 것 : 논리적 모델링

(2) 데이터베이스 구축에 필요한 걸 정하는 것 : 물리적 모델링

데이터 모델

데이터 모델은 데이터를 사용하려는 목적에 맞게 정리하고 체계화 해놓은 모형을 뜻한다.

Entity (개체)

저장하고 싶은 데이터의 대상

학생, 수업, 교수

Entity: 실제 대상 하나하나 (로우)

Entity Type: 일반화한 Entity 종류 (테이블)

Attribute (속성)

Entity에 대하여 저장하려는 내용

학번, 이름, 성별, 입학년도 ...

column

Relationship (관계)

Entity들 사이 연결점

Constraint (제약 조건)

학번 겹치면 안된다.

최소 한 명의 지도교수

데이터 모델링 목적

저장하고자 하는 데이터에서 Entity, Attribute, Relationship, Constraint 파악

데이터베이스를 구축할 때 기반이 될 모델 만들기

Relational 모델

테이블 사이에 맺어지는 관계가 아니다.

Relation: 테이블을 의미하는 수학적 표현

Relational Model : 데이터를 relation, 즉 테이블로 정리해서 표현한 모델, 모델링 할 시에는 실제 모델과는 다른 Model 사용 (ERM) + Relational Model도 같이 사용

Relationship : 테이블들 사이 맺어지는 관계 (foreign key), Parent/Child(foreign key로 연결)

Entity Relationship Model (ERM) : 개체 관계 모델

데이터 모델 스펙트럼

개념 모델 (Conceptual Model) : 경영인, 또는 기획자

논리 모델 (Logical Model) : 개발자 구체화

물리 모델 (Physical Model) : 데이터베이스 구축

아래로 갈수록 구체적이다.

좋은 데이터베이스

여러 데이터를 넣을 때 (1) row를 늘리면 중복이 생기고, column을 만들면 데이터 낭비가 심하다. (2) 테이블을 두 개로 늘리면 중복되는 데이터가 저장되지 않고, NULL이 생기지 않고,데이터가 늘어날 때 테이블 구조가 변하지 않는다.

새로운 데이터가 추가돼도 최대한 기존 구조를 바꾸지 않게 한다.

모델링을 제대로 하지 않으면, 데이터베이스에 다양한 문제들(데이터 정확성이 떨어지거나, 성능 저하가 되는)이 생길 수 있다.

2. 논리적 모델링

비즈니스 룰

모델링의 시작은 Entity, Attribute, Relationship 파악이다.

이를 비즈니스 룰(사업규칙)으로 정리하면 좋다.

비즈니스 룰은 특정 조직이 운영되기 위해 따라야 하는 정책, 절차, 원칙에 대한 간단명료한 설명이다. 즉, 웹사이트면, 페이지에서 제공하는 모든 기능에 대한 규칙이다.

Entity, Attribute, Relationship

모든 명사는 entity 후보이다.

모든 동사는 relationship 후보이다.

하나의 값으로 표현할 수 있는 명사는 attribute 후보이다.

attribute과 relationship들의 특성에 따라서 모델링이 바뀔 수 있다.

여러 값을 갖는 Attribute

계속해서 column을 추가하는 방법은

(1) Null이 많이 생기고, 공간의 낭비가 생긴다.

(2) 컬럼을 몇 개를 만들어야 하는지가 애매해진다.

(3) 조회가 복잡하고 비효율적이다.

그러므로 분리해서 하나의 table로 만들고, foreing key (userId)로 연결한다.

모델링 기본기 정리

비즈니스 룰

Entity, Relationship, Attribue 후보 찾기

Attribute 후보 찾기 예외 경우

(1) 하나의 값으로 표현할 수 있는 명사가 아닌 경우, 하나의 값으로 표현할 수 있더라도 하나의 entity가 여러 개의 값을 가져야하는 경우

(2) 컬럼이 아니라, 새로운 테이블(entity)로 만든다.

카디널리티 (Cardinality)

Entity 사이의 관계

1:1 (일대일)

1:N (일대다)

M:N (다대다)

카디널리티 파악을 해야 한다.

카디널리티는 비지니스 룰에 따라 다르다.

카디널리티 ERM에서 나타내기

Corw's foot 표현법

최대 카디널리티

최소 카디널리티

종합

일대일, 일대다 관계 모델링

1:1

(1) 둘 중 하나 혹은 둘 다에 foreign key 추가

(2) 구조적으로 NULL이 생기는 문제를 최소화하기 위해서, 일반적으로 최소 카디널리티가 0인 테이블에 foreign key를 만들어서 모델링한다.

1:다

(1) "다"에 foreign key 추가

다대다 관계 모델링

M:N

다대다 관계에 있는 두 Entity는 테이블 두 개만으로 자연스럽게 표현할 수 없다.

연결 테이블 (Junction Table)

ex) favorite (찜하기)

다대다 관계에 있는 두 Entity는, 새로운 Entity + 두 개의 일대다 관계로 모델링한다.

다대다 관계에서는 동사도 entity 후보가 될 수 있다.

ERM 활용

ERM으로 business rule을 역으로 파악하는 것이 가능하다.

직관적으로 파악이 가능하기 때문에, 데이터를 만들고 수정할 떄 뿐만 아니라 데이터베이스를 설명하거나 파악하고 싶을 때도 활용이 가능하다.

3. 정규화

데이터베이스 이상 현상 (anomaly)

데이터베이스에서 삽입, 업데이트, 삭제를 제대로 할 수 없게 되는 경우

삽입 이상

(1) 새로운 데이터를 자연스럽게 저장할 수 없는 경우

(2) 어느 값이 NULL

업데이트 이상

(1) 데이터를 업데이트 했을 때, 정확성을 지키기 어려워지는 경우

(2) 모든 값를 바꿔야 한다.

삭제 이상

(1) 원하는 데이터만 자연스럽게 삭제할 수 없는 경우

(2) 이름을 삭제하면, 회사까지 삭제

데이터 모델링을 제대로 하지 않았기 때문에 발생한다.

정규화 (Normalization)

데이터베이스의 테이블이 잘 만들어졌는지 평가하고, 잘 만들지 못한 테이블을 고쳐나가는 과정

테이블을 정규형(normal form)이라고 불리는 형태에 부합하게 만들어나간다.

(1) 정규형: 특정 규칙들을 만족하는 테이블의 형태

(2) 문제가 있는 column을 다른 테이블로 옮김

정규형 (Normal Form)

(1) 1NF, 2NF, 3NF ...

(2) 순서에 따라 규칙이 누적된다.

정규형을 적용했을 때

데이터베이스에서 삽입, 업데이트, 삭제 이상을 없앨 수 있다.

새로운 종류의 데이터를 추가할 때 테이블 구조 수정을 많이 하지 않아도 된다.

데이터베이스 구조를 단순화해서 사용자가 더 쉽게 이해할 수 있다.

데이터 모델을 만들고, 데이터베이스에 구현하기 전에 적용하면 좋다. 데이터베이스를 고치는 과정은 복잡하기 때문이다.

Relational Model로 이해한다. (ERM보다 이해하기 쉽다.)

1NF

테이블 안 모든 로우의 모든 컬럼 값들은 나눌 수 없는 단일 값이어야 한다.

확대 해석을 하면 "나눌 수 없는 단일 값"은 테이블 안에 있는 모든 값들이 무조건 하나의 값만 가져야 된다는 의미이다.

서로 같은 여러 값이 저장돼있을 때

해당 칼럼을 하나의 테이블로 분리한다.

서로 다른 여러 값이 저장돼있을 때

column을 여러 개로 분리한다.

ex) first_name, middle_name, last_name

나눌 수 없는 단일 값 기준

표현 자체가 불완전한다.

컬럼을 사용할 때 온전히 그 단위 자체로 사용하는지, 저장하는 값들을 일부만 사용할 수 있는지 파악한다.

함수 종속성(Functional Dependency)

attribute 사이에서 생기는 관계

x의 값에 따라서 y의 값이 결정될 때, y는 x에 함수 종속성이 있다고 한다.

x → y

ex) email → {name, age, gender} (name, age, gender에 따라 나머지 값이 결정되지는 않는다.), product → {sezie, price}, {user , product} → score

이행성 (함수 종속성이 넘어간다.)

ex) product → brand → brand_country

Candidate Key

하나의 로우를 특정 지을 수 있는 attribute들의 최소 집합

{id, user_id, product_id} candidate key, prime attribute

(1) id는 candidate key이면서 primary key

(2) 나머지는 non-prime attribute

2NF

1NF에 부합해야 한다. (테이블 안 모든 값이 나눌 수 없는 단일 값이어야 한다.)

테이블에 candidate key의 일부분에 대해서만 함수 종속성이 있는 non-prime attribute가 없어야 한다.

(1) user_id → age, product_id → price

(2) age와 price는 candidate key의 전체 {user_id, product_id}가 아닌 일부에만 의존하고 있음

(3) non-prime attribute를 옮기면 된다. (age는 user 테이블, price는 product 테이블로 옮겨준다.)

3NF

2NF에 부합해야 한다.

테이블 안에 있는 모든 attribue들은 오직 primary key에 대해서만 함수 종속성이 있어야 한다. (테이블의 모든 attribute는 직접적으로 테이블 Entity에 대한 내용이어야만 한다.)

ex) event, event_num이 candidate key, winner,age가 non-prime attribute

(1) winner→age이므로 2NF에는 부합한다.

(2) 하지만 id→winner→age로 age는 primary key에 대한 이행적 함수 종속성이 있다.

(3) 3NF는 이행적 함수 종속성도 허용하지 않는다. user_id foreign key를 만들고 user 테이블로 분리한다.

비정규화

성능 개선을 위해, 정규형에 부합하는 테이블을 정규형을 지키지 않게 바꾸기도 한다.

(1) 정규화로 인해서 데이터가 이곳저곳 너무 많이 퍼져있으면, 퍼져있는 데이터를 다시 모을 때 속도가 느려질 수 있다. 비정규화를 하면 이런 정보들은 한 테이블에 저장할 수 있기 때문에 조금 더 빠르게 조회를 할 수 있다.

(2) 하지만 이상 현상 등 문제점들이 많이 생길 수 있다.

비정규화의 문제점아래 문제점들에 대해 인지를 하고 있어야 한다.

(1) 데이터가 너무 퍼져있어서 조회 성능 문제가 심각한 수준으로 일어나는게 확실할 때 사용한다.

(2) 테이블을 삽입, 업데이트, 삭제하는 것보다 조회하는 용도로만 사용하고 있을 때 권장한다.

4. 물리적 모델링

물리적 모델링 범위

SQL 또는 ORM 같이 코드로 정할 수 있는 내용들 위주로 설명한다.

"시스템 디자인"은 추후에 다룬다.

네이밍

물리적 모델링: 데이터베이스에 실제로 저장하는 내용에 최대한 가깝게 데이터 모델을 만드는 과정

Phil Karlton: 컴퓨터 과학에서 어려운 건 딱 두 가지가 있다: 캐시 무효화와 네이밍이다

단수/복수 정하기

테이블 이름 단수형 : 복수형은 일관성이 없어서

테이블 이름 복수형: 여러 개의 데이터를 저장하므로 의미적으로 자연스럽다. SELECT .. FROM users

컬럼 이름 단수형: 모든 column의 값은 분리불가능한 단일값을 사용해야 하기 때문이다.

대문자/띄어쓰기 정하기

user, user_id

User, UserId

user, userId

줄임말 정하기

줄임말 x : 일일이 치는게 비효율적이다.

줄임말 o: 뭘 의미하는지 직관적인 이해가 힘들 수 있다.

규칙

어떤 규칙을 정하는지보다, 정해진 규칙을 잘 따르는 게 중요하다.

처음에 규칙을 최대한 명확하게 정하고 따라야 한다.

데이터 타입

데이터 타입

각 컬럼이 "어떤" 데이터를 저장하는지에 대한 내용

데이터베이스의 가장 기본적인 제약 사항

데이터 타입을 잘 정해야 하는 이유

(1) 데이터의 정확성을 지켜준다.

(2) 데이터베이스에서 지원하는 다양한 연산, 함수를 사용하기 편하다.

(3) 데이터베이스 용량을 최적화한다. : 데이터베이스의 용량이 클수록 느려지므로, 똑같은 데이터를 저장하되 용량은 가장 작게 사용하는 것이 좋다. INT보다도 TININT을 사용하면 1/4 용량을 사용할 수 있다.

데이터베이스 타입 정리(MySQL)

(1) Numeric types

정수형 : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

실수형: DECIMAL, FLOAT, DOUBLE

(2) Data and Time Types

DATE: '2020-03-26'

DATETIME: '2020-03-26 09:30:27'

TIMESTAMP: '2020-03-26 09:30:27' , time_zone 정보도 함께 저장

UTC(국제 표준 시)

TIME: '09:27:31'

(3) 문자열 타입(String type)

CHAR(n) : 최대 n자의 문자열 저장, 0≤n≤255, 고정 길이 타입

VARCHAR(n): 가변 길이 타입, 0≤n≤65,535(2^16-1)

TEXT: 최대 65535자 저장

제약 조건(constraint)

값이 특정 범위, 길이, 또는 값이어야 한다.

데이터베이스에 걸어주는 것이 아니라 찾아내서 오류를 내서 원하지 않는 값이 저장되는 것을 미리 막아야 한다.

SQL 혹은 ORM으로 적용

선형 vs 이진탐색

선형 탐색 (Linear Search)

순차적으로 탐색

O(n)

이진 탐색 (Binary Search)

정렬 후 중간 값 확인

배열 요소가 짝수 개여서 중간 요소가 없을 때는 중간 지점에서 하나 앞 원소 사용

정렬되어 있을 때만 적용 가능하다.

O(log(n))

인덱스

"데이터가 정렬돼있으면 원하는 데이터를 더 빠르게 찾을 수 있다"라는 특성을 데이터베이스에 적용한 개념

ex) 책에서의 마지막 페이지, 가나다순 정렬, 이진탐색 활용 가능

이메일로 유저를 찾고 싶을 때

(1) 이메일 테이블 생성

(2) user 테이블의 email 컬럼에 대해서 인덱스를 저장

원하는 조건의 row를 더 빠르게 찾기 위해서 특정 컬럼 값들을 정렬해서 저장해놓은 것을 인덱스라고 부른다.

Clustered vs Non-Clustered 인덱스

Clustered 인덱스

테이블 자체를 특성 순서로 저장하는 인덱스

조회 속도가 굉장히 빠르다.

인덱스를 하나밖에 못 만든다.

언어 사전과 비슷한 개념

Non-Clustered 인덱스

테이블 자체는 그대로 놔두고 다른 곳에 순서를 저장

인덱스를 모든 컬럼에 대해서 만들 수 있다.

Clustered 인덱스보다는 조금 느리다.

일반 책의 색인, 인덱스와 비슷한 개념

인덱스 중복되는 값들

컬럼에 중복되는 값들이 있어도 인덱스는 충분히 잘 작동한다.

Composite 인덱스

인덱스는 단순히 하나의 컬럼이 아니라, 여러 개의 컬럼에 대해서, 합쳐서도 만들 수 있다.

주의할 점

개별 컬럼들에 인덱스를 추가하는 것과, 여러 컬럼들에 대한 인덱스를 추가하는 건 두 개의 다른 인덱스이다.

여러 컬럼들에 대한 인덱스를 만들 때, 순서가 중요하다.

가장 왼쪽에 조건으로 가장 많이 사용하는 컬럼을 사용하고, 오른쪽으로 갈수록 조건으로 덜 사용하는 컬럼을 사용한다. 그러면 인덱스를 여러 개를 저장하지 않고도, 조회를 빨리할 수 있는 효과가 있다.

인덱스 단점과 사용

용량 문제

인덱스 업데이트 문제: 하나의 로우의 값을 바꾸면, 해당 컬럼이 포함된 모든 인덱스를 수정해야 한다. 조회는 빠르게 할 수 있지만 삽입, 업데이트, 삭제는 더 느리게 만든다.

인덱스 추가 기본 원칙

(1) 모든 primary key에 대해서 인덱스를 만들어준다.

(2) 모든 foreign key에 대해서 인덱스를 만들어준다.

(3)특정 조회 쿼리가 너무 느려지거나, 느려질 게 확실한 경우 조회에 사용되는 컬럼들에 대해서 인덱스를 만들어준다.

SQL로 인덱스 만들기

Clustered 인덱스 만들기

MySQL에서는 자동으로 각 테이블의 primary key (주로 id 컬럼)에 대한 clustered 인덱스가 만들어진다.

다른 컬럼을 clustered 인덱스로 사용하고 싶을 때

clustered 인덱스는 테이블당 하나씩 밖에 있을 수 없기 때문에, 먼저 기존 인덱스를 삭제한 후 진행한다.

CREATE CLUSTERED INDEX index_name ON table_name (column_name);

Non-clustered 인덱스 만들기

CREATE INDEX index_name ON table_name (column_name);

Composite 인덱스 만들기

Clustered/Non-clustered 인덱스 모두, 하나의 컬럼이 아니라, 여러 개의 컬럼에 대해서 composite 인덱스를 만들 수 있다.

CREATE INDEX index_name ON table_name (column_name_1, column_2, ...);

인덱스 확인하기

SHOW INDEX FROM table_name;

인덱스 삭제하기

DROP INDEX index_name ON table_name;

인덱스를 삭제하기 위해서는 항상 인덱스 이름을 알고 있어야 된다. 조회문을 사용해서 확인할 수 있다.

인덱스 사용하기

인덱스가 있으나 없으나, 똑같이 SELECT 문을 사용하셔서 조회한다.

DBMS가 알아서 인덱스를 사용할 수 있는 쿼리들에 대해서는 인덱스를 사용을 해준다.