데이터베이스 관계(Relationship)
관계형 데이터베이스(Oracle, MySQL, MS-SQL, SQLite 등등)는 엔티티(Entity)끼리 관계를 맺을 수 있어서 관계형 DB라는 이름이 붙었다.
이때 관계는 두 엔티티가 서로 관련이 있을 때를 말하는데, 1:1, 1:N, N:M 관계를 맺을 수 있다.
엔티티간의 관계를 알기 전에 알아야할 지식에 대해 먼저 살펴보자.
스키마(Schema) & 쿼리 디자인(Query Design)
스키마(Schema)란?
- 스키마란 데이터베이스를 구성하는 레코드의 크기, 키(key)의 정의, 레코드와 레코드의 관계, 검색 방법 등을 정의한 것을 말한다. 즉, 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명을 말한다.
- 데이터베이스의 청사진과 같다.
엔티티(Entity)란?
데이터베이스 디자인의 시작은 entity를 정의하는데서 시작한다. 여기서 entity란 데이터베이스에 입력될 데이터의 집합을 의미한다. 다시 말해 사람, 장소, 물건, 사건, 개념 등과 같은 명사와 관련된 데이터의 집합인데, 데이터베이스에서는 하나의 entity의 속성을 저장하기 위해 한 개 이상의 테이블을 사용한다. 그리고 그 테이블의 주제를 entity라고 표현한다.
예를 들어 회원관리 앱을 개발할 때는 Users라는 entity(table)가 있을 수 있다. 또한 특정 Users의 address를 저장하기 위해서 addresses라는 entity(table)가 필요하다.
entity개념이 잘 와닿지 않는데, 자꾸 나오는 단어라 헷갈린다면 이렇게 이해해보자.
entity = 테이블, entity의 객체 = 테이블의 row
Entity에 관해선 더 상세한 내용이 있지만 해당 글에서는 간략하게 소개만 했다.
관계(Relationship) 필요 지식
데이터베이스에서는 두 개의 엔티티간의 관계를 정의해야 한다. 이처럼 관계를 표기하기 위해서는 아래 3가지 개념이 필수적이다.
1) 관계명 : 관계의 이름
관계명이란 엔티티가 관계에 참여하는 형태를 지칭하는 이름으로, 두 개의 엔티티에 대한 것이기 때문에 하나의 관계는 2개의 현재형으로 표현한 관계명을 가진다.
2) 관계 차수
두 개의 엔티티 관계에서 참여자의 수를 표현하는 것을 관계차수라고 한다. 가장 일반적인 관계차수 표현방법은 1:1, 1:N, N:N이다. 관계 차수에서 가장 중요하게 고려해야 할 사항은, 한 쪽 엔티티와 관계를 맺은 엔티티 쪽이 하나의 객체만을 가지는지, 혹은 여러 개의 객체를 가질 수 있는지를 파악하는 것이 중요하다.
위 그림은 이와 같은 관계 차수와 관련하여 그린 ERD(Entity Relationship Diagram)으로, 엔티티간의 관계를 시각화한 다이어그램을 말한다.
3) 관계 선택 사양(Optionality)
관계에서 항상 참여해야 하는지(필수관계인지) 아니면 참여할 수도 있는지(선택관계인지)에 대한 것을 관계 선택 사양이라고 한다. 아래처럼 엔티티가 항상 관계에 참여하는지 (필수 참여) 아니면 선택적으로 관계에 참여하는지(선택참여)를 구분할 수 있어야 한다.
위 그림을 예로 들어보면, 학생 엔티티는 수업 엔티티와 관계가 있을 수도 있고, 없을 수도 있다. 따라서 학생 엔티티를 기준으로 학생-수업 관계는 선택참여가 된다. 따라서 동그라미 표시를 한다. 반대로 학생이 듣지 않는 수업에 대해서는 고려하지 않는 경우에, 수업 엔티티는 학생 엔티티와의 관계가 필수적이기 때문에 수업 엔티티를 기준으로 수업-학생 관계는 필수 참여가 된다. 따라서 이때는 작대기를 하나 그어서 표시한다.
Keys
key는 테이블의 관계와 유일성(uniqueness)을 설정하는 constraint중 하나다.
- 테이블의 특정 row(record, tuple)를 식별하거나
- 다른 테이블의 특정 row(record, tuple)를 참조할 때 사용된다.
가장 대표적인 두 종류의 key는 Primary Key(PK)와 Foreign Key(FK)가 있다. 사실 이거 외에도 후보키, 대체키, 슈퍼키 다른 종류의 키가 있는데, 해당 내용은 다음 포스팅을 참고하자.
[DataBase] 데이터베이스 키(KEY) 종류
Relation 주로 테이블(Table)과 같은 의미로 사용되며, 데이터의 집합을 의미한다. 튜플(Tuple)과 어트리뷰트(Attribute)로 구성되어있다. Table 중 데이터베이스에서 사용되기 위한 조건을 갖춘 것이 relati
s-y-130.tistory.com
Primary Key (기본 키)
Primary Key는 이름에서 알 수 있듯이 테이블에서 가장 기본적인 속성을 의미한다. 테이블에서 특정 row(tuple/record)를 식별하기 위해 사용되는 값이다. 데이터베이스에서 테이블을 생성할 때 하나 또는 그 이상의 항목을 기본키로 설정할 수 있다.
식별자로 동작하기 위해서는 아래의 제약조건이 필수적이다
- NOT NULL : 아무런 값이 없는 상태 값인 null 값을 가질 수 없다.
- UNIQUE : 다른 row와 중복되어 나타낼 수 없는 단일 값을 가진다.
여기서 주의 할 것은, 테이블에서 하나만 가질 수 있기 때문에 똑같이 NOT NULL, UNIQUE한 constraint를 가지고 있다고 하더라도 모두가 기본키가 될 수 없다.
그리고 일반적으로는 id라는 필드를 primary key로 지정하는 경우가 많다.
primary key를 설정하는 방법은 아래 두가지 방법이 있다. (사실 데이터베이스마다 다르다)
방법 1)
CREATE TABLE {TABLE NAME}
(
{field name} {field type} PRIMARY KEY,
...
)
#예:
CREATE TABLE Persons
(
UserId INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Job VARCHAR(100),
);방법 2)
CREATE TABLE {TABLE NAME}
(
{field name} {field type},
...,
CONSTRAINT {constraint name} PRIMARY KEY ({field name}...)
)
# 예:
CREATE TABLE Persons (
UserId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Job VARCHAR(100),
CONSTRAINT PK_Id PRIMARY KEY (UserId)
);
#복합키를 PRIMARY KEY로 지정하는 예
CREATE TABLE users_books (
user_id int NOT NULL ,
book_id int NOT NULL,
checkout_date timestamp,
return_date timestamp,
PRIMARY KEY (user_id, book_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(id) ON UPDATE CASCADE
);
Foreign Key (외래 키)
기본적으로 FOREIGN KEY 제약 조건을 설정한 필드를 외래키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 한다. 관계를 맺고 있는 테이블 중 참조되는 릴레이션의 기본키 필드 혹은 UNIQUE 제약조건이 설정된 필드와 대응되어 릴레이션간에 참조 관계를 표현하는 속성을 의미한다.
Foreign Key를 설정하는 방법은 아래와 같다.
CREATE TABLE {table name}
(
{field name} {field type},
...,
FOREIGN KEY ({field name}) REFERENCES {another table name}({another table field name})
)
# 예:
CREATE TABLE Persons (
UserId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
ClassId INT NOT NULL,
PRIMARY KEY (UserId),
FOREIGN KEY (ClassId) REFERENCES classes(ClassID)
);
#복합 키를 외래키로 지정하는 예
CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER NOT NULL,
ref_type INTEGER NOT NULL,
sub_descr CHAR(20),
FOREIGN KEY (ref_num, ref_type) REFERENCES accounts(acc_num, acc_type));Foreign key를 사용하는 예시
colors라는 테이블과 shapes라는 테이블이 있다고 해보자. 이 때 shape가 여러개의 color를 가질 수 있다고 했을 때 아래와 같이 shapes에 color_id라는 column을 colors테이블의 foreign key로 설정하여 아래 그림과 같은 관계를 만들 수 있다.
참조무결성 ( Referential Integrity )
referential integrity(참조 무결성)란 참조관계에 있는 2개의 테이블간의 데이터 무결성, 즉 데이터가 항상 일관된 값을 유지되는 것을 의미한다. 쉽게 말해 참조되는 테이블의 기본키에 대한 외래키가 존재하는 한 해당 row는 수정되거나 삭제될 수 없다. 만약 해당 레코드를 수정하거나 삭제하면, 다른 테이블의 외래키가 삭제되거나 수정된 레코드를 가리켜 참조 무결성 제약에 위배가 되기 때문이다.
예를 들어 shapes테이블의 color_id가 Foreign key이고 그 값이 '1'이면 실제로 colors 테이블에 '1'이 있어야 하고 두 값은 항상 같아야 한다. 만약 변경해야 하거나 삭제해야 하는 상황에는 Cascade Action을 사용해야 한다.
이렇게 참조 무결성 개념이 있는 이유는 다른 테이블에 존재하지 않는 데이터를 참조하지 않도록 보장하고, 사용자의 실수로 인해 관련 데이터가 삭제되거나 수정되는 것을 막기 위해서다.
관계(Relationship) 종류
이처럼 두 table간의 관계를 mapping cardinality로 표현하고, Foreign key를 통해 두 개의 테이블간의 관계를 설정할 수 있다
이제부터는 3개의 기본적인 테이블간의 관계에 대해 알아보자.
1) 1 : 1 : 한 유저는 하나의 주소를 가진다
2) 1 : N : 한 권의 책은 여러 개의 리뷰를 가질 수 있다.
3) N : N : 한 유저는 여러 권의 책을 예약할 수 있고, 한 권의 책은 여러 명의 유저에게 대여될 수 있다.
1 : 1 (일대일 관계)
1 : 1 관계란 참조하는 테이블의 row(entity 객체)와 참조되는 table의 row(entity 객체)가 단 하나의 관계를 가지는 것을 의미한다.
다음과 같이 Users 테이블과 Phonebook 테이블이 있다고 가정한다. Users 테이블은 ID, name, phone_id를 가지고 있다.
이 중 phone_id는 외래키(foreign key)로써, Phonebook 테이블의 ID 와 연결되어 있다.
Phonebook 테이블은 ID와 phone_number를 가지고 있다.
각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하다면, Users 테이블과 Phonebook 테이블은 1:1 관계(One-to-one relationship)이다.
그러나 1:1 관계는 자주 사용하지 않는다.
1:1로 나타낼 수 있는 관계라면 Users 테이블에 phone_id를 대신해 phone_number를 직접 저장하는 게 나을 수 있다.
1 : N (일대다 관계)
1:N 관계는 참조되는 테이블에서 하나의 row(entity 객체)가 참조하는 테이블의 여러 row(entity 객체)를 가질 수 있는 관계를 의미한다. 여기서 주의 할 점은 참조되는 테이블의 row는 한개라는 점이다. 1:N관계는 실제 DB를 설계할 때 자주 사용되는 관계이다.
1:N 구조에서는 보통 primary key - foreign key를 사용하여 관계를 맺는다.
Foreign key(외래키)는 다른 table의 Primary key column과 연결되는(참조되는) table의 column을 의미한다. 즉, 두 table을 연결할 때 한 table의 외래키가 다른 하나의 table의 기본키가 된다.
예를 들어
users = 참조되는 테이블, reviews = 참조하는 테이블 : 한 유저는 여러개의 리뷰를 쓸 수 있고, 리뷰는 여러 명의 유저를 가질 수 있다.
그림의 예시와 같은 상황에서 유저의 정보가 변경된다고 해도, 리뷰 table은 수정할 필요가 전혀 없게 되어 효율적인 데이터베이스 운영이 가능해진다.
또한 기본적으로 Foreign Key를 갖고 있는 테이블이 "다" 쪽에 해당한다.
N : N (다대다 관계)
N : N 관계는 관계를 가진 양쪽 entity 모두가 서로에게 1:N 관계를 갖는 것을 의미한다. 즉, 서로가 서로를 1 : N 관계로 보는 것이다.
예를 들어보면 한 유저는 여러 개의 책을 예약할 수 있고, 한 권의 책은 여러 유저에 의해 대여될 수 있다. 이런 경우가 바로 N : N 관계가 될 수 있다.
N : N 관계의 경우에는 두 개의 테이블의 관계를 이어주는 제 3의 cross reference table(Mapping table)이 별도로 필요하다. 이 테이블은 두 테이블의 Primary Key를 컬럼으로 갖는다.
Users_books 테이블을 보면 1번 유저는 1,2 번 책을 대여하고 있고(1 : N)
2번 책은 유저 1, 유저 2한테 대여되고 있다. (1 : N)
관계 정리
| 관계 | 설명 |
| 1 : 1 | 참조하는 테이블의 row(entity 객체)와 참조되는 table의 row(entity 객체)가 단 하나의 관계를 가지는 구조 |
| 1 : N | 하나의 entity(table)가 관계를 맺은 entity의 여러 객체를 가질 수 있는 구조 |
| N : M | 양쪽 entity 모두가 서로에게 1:N 관계를 갖는 구조. N:M 구조에서는 보통 새로운 table(Mapping table)을 통해서 관계를 맺는다. |
참고