Data Normalization & Relationships: Splitting & Joining data

이 글에서 다룰 내용은 다음과 같다.

1. Data normalization을 이해한다. (테이블을 어떻게 나누어야 하는지)

2. INNER JOIN, LEFT JOIN 등으로 데이터를 결합하는 방법

3. Data relationships의 타입들 (One-to-One, One-to-Many, Many-to-Many)

 

데이터베이스에서 데이터는 Key를 통해서 연결된다. 

한 테이블의 primary key가 다른 테이블의 foreign key로 사용된다. 

Primary key와 foreign key를 통해서 테이블간의 관계를 형성 하는 방식이 전형적인 SQL 세계에서 connection을 형성하는 방법이다.

 

모든 테이블은 최대한 하나의 Primary key를 가질수 있지만 모든 테이블은 여러개의 Foreign key를 사용할 수 있다.

Foreign Key또한 반드시 가질 필요는 없지만, 사용을 통해 다른 테이블의 데이터와의 연결 할 수 있다.

그럼 여기서 드는 의문이, 데이터 베이스의 데이터를 왜 분리 해야 하는 것인가?

만약 모든 데이터를 하나의 table에 보관한다면 테이블간의 JOIN할 필요도 없고 더 쉬운데 말이다.

또한 테이블을 나누려면 어떻게, 무슨 기준으로 데이터를 나누어야 할까?

 

※ 왜 데이터를 하나의 테이블로 유지하는 대신 여러 테이블로 나눠야하는가?

 - 관계형 데이터베이스 (RDBMS)를 다룰 때, 데이터는 기본적으로 정규화 되어야 한다 (normalizd)

 

1. Data Normalization (데이터베이스 정규화)란?

 - 데이터의 불필요한 중복(data redundancy) 데이터의 유지성(data maintainability)를 증가시키는 방법

 - 여러 테이블로 작업하는 data entities에 따라서 데이터를 쪼개고, 테이블을 조직화함으로서

 - 목표 : 그룹화된 데이터를 여러 분리된 값으로 쪼개어서 데이터를 관리/유지를 쉽게 해준다.

 

(1) 한 테이블에서의 데이터를 여러 column으로 나누기

예를 들어서, users라는 테이블에서 full name을 저장한다고 하자. 이 때 발생하는 문제는 user의 이름이 어떻게 저장되어야 하는지에 대한 명확한 규칙이 없다는 것이다. 

같은 내 이름을 Junho Hwang 으로 저장하든, Hwang, Junho 라고 저장하든, Hwang Junho라고 저장하든 이름 값에 대하여 하나의 column에 어떻게 저장되어야 하는지에 대한 명확한 규칙이 없다는것이 문제이다.

이것이 문제가 되는 이유는, 같은 데이터베이스로 작업하는 여러개의 어플리케이션이 있다면, 일관되지 못한 데이터(inconsistent data)의 위험성이 있기 때문에, 같은 종류의 데이터가 여러 variation으로 저장되는데 문제가 발생한다.

이렇게 여러 variation으로 저장한다면 데이터를 쿼리할때 명확한 기준이 없기 때문에 쿼리가 더 어려워 진다.

따라서

① 데이터 유지성 (Data maintainability)와 데이터 사용성의 문제

② 데이터의 불필요한 중복(Data redundancy)

문제가 발생가는데, 이 문제를 해결하기 위해서 데이터를 normalize하여 데이터를 작은 엔티티들로 쪼개는 것이다. 

full name을 한 column에 저장하는 대신, first_name, last_name으로 column을 쪼개어 해결 할 수 있다.

 

(2) 데이터를 여러 테이블로 나누기

목적은 같다. 데이터의 불필요한 중복과 유지성을 증가시키기 위해서 데이터 엔티티 (Data entity)당 하나의 테이블로 데이터를 쪼개는 것이다.

출처 : 아래 reference Udemy 강의

위 테이블이 있을때, User의 정보와 address로 분리해 두개의 data entity를 만들 수 있다. 한 테이블 당 하나의 Data entity를 가지기 위해서 테이블을 나누는 것이다.

분리하는 방법은, 위에서 언급한 것 과 같이, data entity로 각 테이블을 분리하고, id를 통해 primary key와 foreign key의 관계로 users 테이블의 id와 addresses 테이블을 만들어 한 column을 foreign key로 설정해 연결 할 수 있다. 

데이터 엔티티를 분리할때에는, 몇 column들이 연관성이 있고 무언가를 뜻한다면, 같은 data entity로 볼 수 있다.

데이터를 모델링 할때, 데이터 엔티티(data entity)는 작은 것으로 큰 엔티티를 만드는 것이고, 데이블을 Join하는 방법은 나중에 설명할 것이다.

즉 간단히 말해서, 데이터 정규화란 관계형 데이터베이스(RDBMS)의 목표는 Data와 Data entity를 더 작게 나누는 것이다. 그 이유는 이렇게 여러 테이블로 데이터를 나눔으로 인해서 Data Redundancy(불필요한 데이터 중복)을 감소시키고 유지성이 증가하기 때문이다.

 

더 작게 나누되 너무 작게는 또한 나누면 안된다. 모든 column을 분리된 테이블로 관리하는 것은 또한 효율적이지 못하기 때문이다.

그럼 정확히 어떠한 기준으로 테이블을 나눠야 하는가? 

① 하나의 테이블에서 중복된 데이터가 여러 row가 있는것은 피해야한다.

먼저 row들에서 중복 데이터를 가지는 것은 data normalization에서 최적화 되지 못했다는 명백한 지표이다(Data redundancy). 위의 데이터베이스 예시에서 이 것을 잘 보여준다. 이것이 문제가 되는 이유는, 위의 테이블에서 Teststreet값이 업데이트 되면, Teststreet을 address_street 값으로 가지는 모든 row를 변경해줘야 하기 때문이다.

 

② 위의 address_street, address_num, address_city 같이 같은 prefix를 공유하고 있다면, 독립적인 data entity일 것이라는 지표가 된다.

 

(3) 이렇게 정규화를 하는 목적은 무엇일까?

첫째는, 여러번 언급했다 시피, Data redundancy를 줄이고, data maintainability를 증가시킨다.

둘째는 CUD 변칙(Anomaly)을 피하기 위해서이다.

출처 : 아래 reference Udemy 강의

2. CUD Anomalies

(1) Creation Anomaly (삽입 이상)

 : 불완전한 데이터를 삽입하는 이상이 발생 할 수 있다. 위의 예시에서, Julie라는 새로운 선생님이 왔는데 아직 course가 배정되지 않았을 경우, course는 삽입할 내용이 없는, 불완전한 데이터를 삽입할 수 밖에 없다.

 

(2) Update Anomaly (수정 이상)

 : 많은 row들이 한 entity가 바뀌면 모두 업데이트 되어야 한다. 위의 예시에서, Max가 Junho라는 이름으로 개명했을 경우, 모든 row를 하나하나 수정해주어야 하기 때문에 extra work, cost extra performance, error prone 문제점을 발생시킨다.

 

(3) Delete Anomaly (삭제 이상)

 : 위의 예시에서, Max 학생을 삭제할때 MongoDB 수강생이 Max 밖에 없을경우, 코스 까지 같이 삭제되는 이상이 발생한다. 

 

이러한 이유 때문에 하나의 테이블이 아닌 여러개의 테이블로 나누어 데이터의 불필요한 중복과 정규화 되지 않은 데이터로 인해 유발 가능한 에러를 피하기 위해 유용하다.

 

3. 정규화 6가지 단계.

 

https://mangkyu.tistory.com/110?category=761304 

[Database] 정규화(Normalization) 쉽게 이해하기

출처 : 아래 reference Udemy 강의

매우 이론적이라 강의에서는 자세히 설명하지 않았다. (특이 상황에 사용되는 것도 있기 때문에)

하지만 간단한 룰은 다음과 같다.

같은 테이블에서 연관성이 있는 데이터 엔티티(data entity)를 섞는것을 피해라.

하나의 테이블 셀에 여러개의 값들을 피하되, 기본 데이터를 여러개의 테이블로 나누는 것 또한 피해라.(중도를 찾아야 한다.)

한 테이블당 하나의 데이터 엔티티, 하나의 셀 당 하나의 값만 들어가도록 하고, 처음 볼때 생각한 데이터 엔티티 보다 더 많은 데이터 엔티티가 있을 수 도 있다는것을 알아야한다.  

출처 : 아래 reference Udemy 강의

예를들어 위와 같이, 반복되는 country column경우, data entity로 분리하여 테이블을 형성 할 수 있다는 것이다. (이름도 중복이 될 수 있지만, 동명이인이 같은 사람을 가리키지는 않지만 국가는 같은 이름의 경우 같은 국가를 가리키는 것이기 때문에 차이점이 있다)

 

<예시> 데이터베이스에 회원가입을 할때, 회원이름, 이메일, 주소의 거리명, 우편번호, 도시이름을 받는다.

 

이때, 데이터베이스 생성을

User 테이블에 id, first_name, last_name, email, address_id (Addresses 테이블의 id와 연결)

Addresses 테이블에 id, street, home_number, city_id (cities 테이블의 id와 연결)

cities 테이블에 id와 name 컬럼으로 

세 개의 테이블로 분리하여 생성 할 수 있다.

city의 경우, address에 포함시켜도 되지만, row간의 중복이 있을 수 있고, 그것이 같은 도시를 가리키는 것이기 때문에 data redundancy를 줄일 수 있기에 다른 데이터 엔티티로 분리해 테이블을 형성하는것이 바람직하다.

 

* Foreign key 설정시, naming convention은 <연결된 데이터 엔티티의 이름>_<관련 테이블에서의 연결되는 column의 이름> 이다. 따라서 위에서 address_id, city_id로 이름을 지은 것이다.

또한, 외래키 설정시 참조하는 primary key의 데이터 타입을 일치시켜야 한다.

 

* 주의할 점: 데이터를 삽입하고, 테이블을 drop을 통해 삭제할때, 순서가 중요하다. address_id가 NOT NULL로 설정되어 있기 때문에, user부터 데이터 삽입을 할 수 없고, city -> address -> user순으로 넣어야 한다. 

또한, cities 테이블에 데이터 삽입 시, name에 UNIQUE 키워드를 추가할 수도 있고, name column자체를 primary key로 설정 가능하다. (primary key가 반드시 incrementing id일 필요는 없다는 것이다.)

 

4. 테이블간 연관된 데이터를 쿼리하는 방법

정규화를 통해 데이터 엔티티 별로 나눈 각기 다른 테이블로 부터 데이터를 하나의 result set으로 merge하는 방법

→ <INNER JOIN ... ON ...>문이 필요한 이유이다. 이는 여러 테이블로 부터 데이터를 SELECT 할 수 있게 해준다.

 

(1) INNER JOIN

형식 :  <Result set> INNER JOIN <Result set에 붙히고 싶은 Table명> ON <두 Table간 같은 data column>

SELECT u.id, first_name, last_name, street, house_number, c.name AS city_name FROM users AS u
INNER JOIN addresses AS a ON u.address_id = a.id
INNER JOIN cities AS c ON a.city_id = c.id
WHERE c.id = 1 OR c.id = 2
ORDER BY u.id DESC;

위의 예시를 보자. 

예시의 의미는, INNER JOIN 왼쪽에 오는 SELECT 문에 의한 결과 Result set에 addresses 테이블을 JOIN하는데, ON 다음에 설정한 어떻게 이 table들이 JOIN 되어야 하는지에 대한 정의에 따라서 결과 값을 보여주는 것이다.

ON은 SQL에게 어떤 Column의 값을 JOIN할지 설정하는 것이다.

 

* SQL Dot Notation : <Table의 이름 or alias> . <Column 이름> 통해서 여러 table간의 column이름이 중복 될때, column이 어느 table의 column인지를 명시해 줄 수 있음.

* Alias(가명) 설정법 : 위 예시에서 볼 수 있듯, table 명 / column 뒤에 AS 를 통해서 설정 가능. 설정하지 않을 시 Alias는 table이름이 default 값임.

* INNER JOIN 대신 JOIN이라 생략해서 쓸 수도 있지만, 여러개 사용시 쿼리가 복잡해지므로 INNER JOIN이라 쓰는게 낫다.

* 여러 JOIN을 위와 같이 사용 가능한데, 2개 이상의 테이블에 나눠져 있는 데이터를 Merge하는것이 빈번하기 때문에 많이 사용한다. 위에서 cities 테이블을 INNER JOIN할때 Result set은 위 addresses 테이블을 INNER JOIN한 결과가 된다.

* WHERE과 ORDER BY를 통해서 Filtering도 사용 가능하다.

 

(2) LEFT JOIN

SELECT * 
FROM users AS u
LEFT JOIN addresses AS a ON a.id = u.address_id;

INNER JOIN과 다른 점은 SELECT문에서 FROM 다음에 오는 LEFT JOIN 왼쪽에 오는 테이블의 모든 Row가 Result set에 포함되는 것이다. INNER JOIN은 ON 다음에서 설정한 조건에 일치하는 데이터만 양쪽 테이블에서 가지고 오지만, LEFT JOIN의 경우에는, LEFT JOIN 왼쪽에 오는 테이블의 모든 ROW가 오기 때문에, 값을 가지지 않으면 NULL값이 온다.

예를들어, 위의 예에서, user 테이블에 있는 row 중 address_id가 없는값은 NULL이 오는 것이다.

 

(3) RIGHT JOIN

RIGHT JOIN도 있는데, 잘 쓰이진 않는다고 한다.

왜냐하면 모든 RIGHT JOIN은 LEFT JOIN으로 대체될 수 있기 때문에, 대부분 LEFT JOIN만 사용하는것이 Convention이라고 한다.

LEFT JOIN에서는 왼쪽 테이블의 모든 row를 포함 시켰다면, RIGHT JOIN에서는 오른쪽에 명시한 테이블의 모든 row를 포함 시키는 것에 차이가 있을 뿐, 작동하는것은 똑같다고 한다.

 

(4) CROSS JOIN

SELECT *
FROM users
CROSS JOIN addresses;

RIGHT JOIN과 같이 잘 사용하지 않는다고 한다. 

다른 JOIN들과 달리 ON 키워드가 없다. 

기능은 첫 테이블과 두 번째 테이블의 모든 Entry를 JOIN한다. 가능한 value 조합을 모두 보여주는 것이다.

위 결과를 보면 users의 결과와 addresses의 결과의 모든 조합 (address row 수 * users row 수) 만큼의 row를 가진 result set을 보여준다.

실제 관계가 없다고 하더라도 모든 value들이 JOIN 되는 것이다. (곱집합, Cartesian product)

- 수학적인 데이터 다룰 때에는 사용할 일이 있겠지만, 현실에서는 잘 안쓰임.

 

(5) UNION 키워드

JOIN 키워드와 비슷하게, 여러 테이블을 합쳐주지만, JOIN에서는 column별로 합쳐 줬다면, UNION 키워드를 사용하면 row끼리 합쳐준다.

SELECT id, first_name FROM users
UNION
SELECT id, street FROM addresses;

하나의 SELECT 문으로 부터 리턴된 Row들과 다른 SELECT 문으로 부터 리턴된 Row들과 merge하고 싶을때 유용하다.

또한 결합되는 두 result set이 같은 column과 data type일때 유용하다 (다르면 붙힐수는 있지만 의미가 없어진다)

→ 여러개 흩어진 테이블에 있는 데이터를 합치는데 사용하는 것이 아닌, 더 많은 데이터를 첨가하기 위해서 사용한다. (보통 다른 데이터와 같은 구조인)

 

5. Foreign Key 제약조건(Constraint)

(1) Foreign Key 제약조건이 필요한 배경과 REFERENCES 키워드

두 테이블이 primary key와 foreign key로 연결되어 있을 때, primary key가 아니더라도 다른 column을 foreign key로 받을 수 있다. 다른 어떠한 column도 관계를 설정하는데 사용할 수 있는데, unique하고 NULL값만 아니면 사용 가능하다. 하지만 Primary key의 경우 두 조건을 모두 만족하기 때문에 사용하는 것이다. 다른 column을 사용은 할 수 있다는 말이다.

여기서 문제는, 예를 들어 users 테이블의 address_id column이 addresses 테이블의 primary key인 id를 참조하여 foreign key로 설정할 때, addresses 테이블로 부터 유효한 id를 사용하도록 확인하는 규칙이 없다는 것이다. 따라서 address_id에는 addresses 테이블의 id column에 존재하지 않는 값도 저장이 가능하다.

이렇게 참조하는 addresses 테이블의 id 값이 삭제될때 문제를 일으킬 수 있는 것에 대해서 SQL standard solution이 있는데, 그것이 바로 foreign key constraint를 사용함으로 써 referential integrity(참조 무결성)를 설정 가능하다.

이는 테이블 생성하거나 ALTER할때, 설정을 추가할 수 있는데, 정확히는 테이블 설정시 이름과 데이터 타입 뿐아니라 REFERENCES 키워드를 통해 설정 할 수 있다.

이를 통해서 Foreign key에 대한 컨트롤이 가능하며 항상 referential integrity를 가질 수 있다.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(300) NOT NULL,
    last_name VARCHAR(300) NOT NULL,
    email VARCHAR(300) NOT NULL,
    address_id INT REFERENCES addresses (id) ON DELETE CASCADE
);

 

REFERENCES <Table> (<Table의 column>) 을 통해서 Foreign key 설정이 가능하고, Mysql에서는 아래와 같이 설정 할 수 있다. 

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(300) NOT NULL,
    last_name VARCHAR(300) NOT NULL,
    email VARCHAR(300) NOT NULL,
    address_id INT,
    FOREIGN KEY (address_id) REFERENCES addresses (id) ON DELETE CASCADE
);

(<column이름>)의 경우 생략 시 primary key를 가리킨다.

ON DELETE CASCADE를 제외하고 REFERENCES 키워드를 통해서 외래키를 설정하는것 만으로도 참조무결성을 가질 수 있기에, users의 address_id에 addresses의 id가 아닌 값이 올 수 없다. 다른 값을 INSERT하면 BLOCK된다.

이 Foreign key 제약조건으로 이 관계를 더 공식적으로 만들 수 있고, referential integrity를 가질 수 있으며 만약 연결되어 있는 데이터가 바뀌거나 삭제될때 무엇이 일어나야 하는지에 대한 control을 가질 수 있다.

 

(2) ON DELETE / ON UPDATE의 ACTION 정의

위에서 언급한 것 같이, 삭제되거나 업데이트 될때 어떠한 ACTION을 설정 할 수 있다. 

사실 ON DELETE가 더 중요한데, 왜냐하면 거의 연결된 value를 업데이트 하지 않기 때문이다 (unique id 경우 거의 안바꿈)

(1) RESTICT : 연결된 레코드 삭제 불가.

(2) NO ACTION (Default) : 연결된 레코드 삭제 불가. RESTRICT와 차이점은 트렌젝션 사용시 (나중에 다룸)

(2) CASCADE : Primary key 삭제되면 Foreign key도 삭제됨.

(3) SET NULL : 연결된 Row가 삭제되면 외래키는 NULL로 세팅됨

(4) SET DEFAULT : 연결된 Row가 삭제되면 외래키는 DEFAULT로 세팅됨 (Default값 가지고 있다면)

 - ON DELETE NO ACTION 설정하지 않아도 디폴트값이라 연결된 Primary key 삭제 불가능하다.

 

6. Data Relationship의 종류

<예시>

출처 : 아래 reference Udemy 강의

위 상황에서 테이블을 intranet_accounts, employees, teams, projects, buildings 테이블로 나눌 수 있고, 관계는 

intranet_accounts와 employees는 1:1,

teams와 employees는 1:n

teams와 buildings 또한 1:n

employees와 projects 는 n:n 관계이다.

 

(1) One-to-One (1:1)

 : 테이블 A의 한 레코드는 테이블 B의 한 레코드와만 정확히 연결될 경우.

e.g) employee가 오직 하나의 인트라넷 계정을 가지고 있을때

위의 예시에서 Intranet_accounts와 employees 테이블의 경우, 참조무결성을 유지하기 위해서 설계되어야 하며, intranet_account가 삭제된다고 employee까지 삭제되게 하면 안되지만, 반대로 employee가 삭제되면 intranet_account가 삭제되어야 한다. 따라서 쌍방으로 ON DELETE 설정하면 안되고, intranet테이블에만 ON DELETE CASCADE를 걸어야 한다. id와 연결하는 새로운 column을 만들 수도 있지만, email의 경우 unique하고 NOT NULL이므로 이를 통해서 관계를 형성 할 수 있다.

CREATE TABLE employees(
    -- id INT PRIMARY KEY AUTO_INCREMENT,
    id SERIAL PRIMARY KEY, -- Postgresql
    first_name VARCHAR(300) NOT NULL,
    last_name VARCHAR(300) NOT NULL,
    birthdate DATE NOT NULL,
    -- email VARCHAR(200) REFERENCES intranet_accounts (email) ON DELETE
    email VARCHAR(200) UNIQUE NOT NULL,
    team_id INT DEFAULT 1 REFERENCES teams ON DELETE SET DEFAULT
);

CREATE TABLE intranet_accounts(
    -- id INT PRIMARY KEY AUTO_INCREMENT,
    id SERIAL PRIMARY KEY, -- Postgresql
    email VARCHAR(200) REFERENCES employees (email) ON DELETE CASCADE,
    password VARCHAR(200) NOT NULL
);

위에서의 핵심은 Primary key가 아닌 email column에 의존할 수 있다는 것이다. 

1:1 relationship의 특징은 두 테이블 모두에 relationship column을 설정 할 수 있다는 것이다.

 

(2) One-to-Many (1:n)

 : 테이블 A의 한 레코드는 테이블 B와 연결된 레코드가 하나 이상일 경우.

e.g) 회사들의 테이블의 한 회사(레코드)에 employee 테이블에 여러 employee가 있을때 

1:n 관계에서는 foreign key를 어떤 테이블에 설정하는지에 대한 선택권이 없다. 왜냐하면 위의 예시에서 buildings 테이블과 teams 테이블이 있을때, buildings 테이블에 foreign key를 추가 할 수 없다. buildings에 team_id column을 만들어 여러개의 id 리스트를 저장하는것은 데이터 정규화에 위배되기 때문이다. 한 column에 여러 value가 들어가는것은 sql의 방식이 아니기 때문이다.

 

(3) Many-to-Many (n:n)

 : 테이블 A의 한 레코드는 테이블 B의 여러 레코드에 연관될 경우.

e.g) employees 테이블과 projects 테이블의 레코드 관의 관계.

이 경우는, foreign key를 연관된 두 테이블 모두에서 추가할 수 없다.

따라서 세 번째 테이블, Intermediate 테이블 (Intersection 테이블)을 추가하는 방법이다.

이 Intermediate 테이블은 다음과 같이 양 테이블의 id값을 저장하여 두 테이블의 관계를 저장하는데 사용된다. 

두 관련된 테이블의 이름을 섞어 intermediate 테이블을 형성하는것이 일반적이다. 

-- Intermediate table => n:n
CREATE TABLE projects_employees (
    id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES employees ON DELETE CASCADE,
    project_id INT REFERENCES projects ON DELETE CASCADE
);

 

Reference : 

Udemy (SQL - The Complete Developer's Guide)

https://www.udemy.com/course/sql-the-complete-developers-guide-mysql-postgresql/

 

SQL - The Complete Developer's Guide (MySQL, PostgreSQL)

https://mangkyu.tistory.com/110?category=761304 

https://hongcoding.tistory.com/147