[DB] 데이터베이스 설계

1. 관계형 데이터베이스

구조화된 데이터는 하나의 테이블로 표현할 수 있습니다. 사전에 정의된 테이블을 relation이라고도 부르기 때문에 테이블을 사용하는 데이터베이스를 관계형 데이터베이스(Relational database)라고 합니다.

• 데이터(data): 각 항목에 저장되는 값
• 테이블(table 또는 relation): 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적된다.
• 칼럼(column 또는 field): 테이블의 한 열
• 레코드(record 또는 tuple): 테이블의 한 행에 저장된 데이터
• 키(key): 테이블의 각 레코드를 구분할 수 있는 값으로 기본키(primary key)와 외래키(foreign key)가 있다.

 

2. 관계 종류

• 1:1 관계

하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우입니다.

User 테이블의 phone_id는 외래키로써 Phonebook 테이블의 phone_id와 연결되어 있습니다.

각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하다면 User 테이블과 Phonebook 테이블은 1:1 관계입니다.

그러나 1:1로 나타낼 수 있는 관계라면 User 테이블에 phone_id를 대신에 phone_number를 직접 저장하는 게 나을 수도 있기 때문에 1:1 관계는 자주 사용하지 않습니다.

 

• 1:N 관계

하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우입니다.

한 명의 유저가 여러 전화번호를 가질 수 있지만 여러 명의 유저가 하나의 전화번호를 가질 수는 없습니다. 관계형 데이터베이스에서 가장 많이 사용하는 관계입니다.

 

• N:N 관계

여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우입니다.

스키마를 디자인할 때에는 Join 테이블을 만들어 관리합니다.

1:N 관계와 비슷하지만 양방향에서 다수의 레코드를 가질 수 있다는 차이점이 있습니다.

customer_package 테이블에서는 고객 한 명이 여러 개의 상품을 가질 수 있고, 여행 상품 하나가 여러 명의 고객을 가질 수 있습니다.

customer_package 테이블은 customer_id와 package_id를 묶어주는 역할입니다. 이렇게 조인 테이블을 생성하더라도 조인 테이블을 위한 기본키(예를 들어, customer_package의 cp_id)는 반드시 있어야 합니다.

 

• 자기참조 관계

때로는 테이블 내에서도 관계가 필요합니다. 예를 들어 추천인이 누구인지 파악하기 위해 사용할 수 있습니다.

User 테이블의 recommend_id는 User 테이블의 user_id와 연결되어 있습니다.

한 명의 유저는 한 명의 추천인을 가질 수 있지만 여러 명이 한 명의 유저를 추천인으로 등록할 수 있습니다.

1:N 관계와 유사해보이지만 일반적으로 1:N 관계는 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법입니다.

 

3. SQL 내장함수

• GROUP BY: 데이터를 그룹으로 묶어서 조회

SELECT * FROM customers
GROUP BY State;

• HAVING: GROUP BY로 조회된 결과를 필터링

SELECT CustomerId, AVG(Total)
FROM invoices
GROUP BY CustomerId
HAVING AVG(Total) > 6.00

• COUNT(): 레코드의 갯수를 헤아릴 때 사용

SELECT *, COUNT(*) FROM Customers
GROUP BY State;

• SUM(): 레코드의 합 계산

SELECT InvoiceId, SUM(UnitPrice)
FROM invoice_items
GROUP BY InvoiceId

• AVG(): 레코드의 평균 계산

SELECT TrackId, AVG(UnitPrice)
FROM invoice_items
GROUP BY TrackId;

• MAX(), MIN(): 각각 레코드의 최대값과 최소값 구할 때 사용

SELECT CustomerId, MIN(Total)
FROM invoices
GROUP BY CustomerId

 

💡 데이터를 조회하는 SELECT문은 정해진 순서대로 동작합니다.

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

1. FROM invoices: invoices 테이블에 접근한다.
2. WHERE CustomerId ≥ 10: CustomerId 필드가 10 이상인 레코드들을 조회한다.
3. GROUP BY CustomerId: CustomerId를 기준으로 그룹화한다.
4. HAVING SUM(Total) ≥ 30: Total 필드의 총합이 30 이상인 결과들만 필터링한다.
5. SELECT CustomerId, AVG(Total): 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구한다.
6. ORDER BY 2: 2번째 컬럼(AVG(Total) 필드)을 기준으로 오름차순 정렬한 결과를 리턴한다.