[Database] DB 설계

데이터베이스의 개념

• 데이터베이스(Database, DB): 데이터의 저장소
• DBMS(Database Management System, 데이터베이스 관리 시스템): 데이터베이스를 운영하고 관리하는 소프트웨어
  - 계층형, 망형, 관계형 DBMS 중 대부분의 DBMS가 테이블로 구성된 관계형 DBMS(RDBMS) 형태로 사용된다.
• SQL(Structured Query Language): 구조화된 질의 언어라는 뜻으로, 관계형 데이터베이스에서 사용되는 언어

DBMS의 종류

DBMS와 같은 소프트웨어는 특정 목적을 처리하기 위한 프로그램이다.

데이터베이스를 사용하기 위해서는 소프트웨어, 즉 DBMS를 설치해야 하는데 대표적으로 MySQL, 오라클(Oracle), SQL 서버, MariaDB 등이 있다.

DBMS의 종류: MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, SQLite, DB2, Access

DBMS의 분류

DBMS의 유형은 계층형(Hierarchical), 망형(Network), 관계형(Relational), 객체지향형(Object-Oriented), 객체관계형(Object-Relational) 등으로 분류된다.
현재 사용되는 DBMS 중에는 관계형 DBMS가 가장 많은 부분을 차지하며, MySQL도 관계형 DBMS에 포함된다.

관계형 DBMS

관계형 DBMS(Relational DBMS)는 줄여서 RDBMS라고 부른다. MySQL뿐만 아니라, 대부분의 DBMS가 RDBMS 형태로 사용된다.

RDBMS의 데이터베이스는 테이블(table)이라는 최소 단위로 구성되며, 이 테이블은 하나 이상의 열(column)과 행(row)으로 이루어져 있다.

SQL: DBMS에서 사용하는 언어

SQL(Structured Query Language)은 관계형 데이터베이스에서 사용되는 언어로, '에스큐엘' 또는 '시퀄'로 읽는다.

국제표준화기구에서 SQL에 대한 표준을 정해서 발표하고 있는데, 이를 표준 SQL이라고 한다.
표준 SQL을 익히면 대부분의 DBMS에 공통적으로 적용할 수 있다.

테이블 설계

테이블 구성하기

개체 또는 사건인 단일 주제를 나타낸다.

예를 들어 위에 있는 테이블에서 '유저', '상품', '후기' 등이 객체이고, '주문 기록', '결제 기록' 등이 사건이라고 할 수 있다.

기본 키를 가진다.

기본키는 레코드를 식별하고 테이블 관계를 설정하기 위해 필요하다.

다중값 또는 다중 부분 필드들을 포함하지 않는다.

- 다중값 필드
하나의 필드에 여러 사진이 들어갈 수 있기 때문에 '후기' 테이블에 '사진들'이라는 필드가 다중값을 갖고 있을 수 있다.

따라서 이 부분은 '후기 사진'이라는 테이블을 따로 만들고 '후기' 테이블과 관계를 맺어주는 방식으로 다중값 필드를 해결할 수 있다.

- 다중부분 필드
'주소'라는 하나의 필드에 서울시 / 강남구 / 역삼동 등 여러 가지 필드로 나뉠 수 있기 때문에, '유저' 테이블에 '주소'라는 다중 부분 필드를 가질 수 있다.

하지만 프로젝트에 따라 '주소'를 수정할 경우, 전체 주소를 한 번에 수정하거나 삭제하도록 기획되어 있기 때문에 주소를 따로 테이블로 빼내어 다중부분 필드를 해결해주지 않아도 된다.

계산된 필드들을 포함하지 않는다.

계산된 필드는 계산에 참여하는 필드의 값이 바뀔 때 갱신되지 않기 때문에 사용하지 않는다.

최종 금액은 전체 금액, 할인 금액, 배달 금액, 적립금 사용 금액에 의해 계산되기 때문에 '결제 기록' 테이블에 '최종 금액'이라는 계산된 필드는 포함하지 않는다.

외래키 외에 불필요하게 중복되는 필드들을 포함하지 않는다.

최소한의 중복 데이터만 포함한다.

'유저' 테이블에 '등급'이라는 필드의 값이 중복되어 나타날 수 있다.

예를 들어 여러명의 유저는 '골드'라는 등급을 가질 수 있는데, 이 때 '골드'라는 등급의 이름이 '블랙'이라는 이름으로 바뀌게 되면 '골드'라는 등급을 가진 모든 레코드가 '블랙' 등급을 가지도록 수정해주어야 한다.

또한 새로운 등급을 추가하고 싶어도 그 등급에 맞는 회원이 회원 테이블에 존재하지 않으면 추가할 수 없다.

따라서 등급 테이블을 따로 빼주고 등급에 따른 적립률 또한 같이 관리하도록 구성하는 것이 좋다.

필드 명세 설정

테이블 구성이 어느 정도 완료되면 각 필드마다 필드 이름, 데이터 종류, 허용 가능한 데이터의 길이, 값의 범위, 유일성, NULL값 허용, 기본값을 어떻게 할 지 등을 정해주어야 한다.

CHAR vs VARCHAR - 이름, 비밀번호, url, 내용 등

CHAR는 고정길이를 가진다. 만약 데이터 타입이 CHAR이고 길이 제한이 10글자라면 CHAR 데이터 타입은 apple을 'apple☐☐☐☐☐'(☐은 예비 공간)과 같이 저장한다. 따라서 저장되는 데이터의 길이가 일정하지 않다면 많은 저장 공간이 낭비된다.

VARCHAR는 가변길이를 가진다. 마찬가지로 10글자를 저장하는 VARCHAR는 apple을 그대로 'apple'로 저장한다. 하지만 VARCHAR는 저장되는 데이터 크기에 딱 맞게 데이터 공간이 배정되기 때문에 더 긴 길이의 문자로 수정해야 한다면 레코드 전체를 다른 곳으로 옮겨서 새로 저장해야 한다.

요약하자면 CHAR는 저장 공간이 낭비되고 VARCHAR는 수정에 대한 성능이 떨어진다.

웹 서비스에서 사용하는 데이터베이스는 조회, 수정, 삽입, 삭제 중 조회의 성능이 가장 중요하다.
따라서 대부분의 문자열 데이터에 조회 성능이 좋은 VARCHAR를 선택하는 편이다.

VARCHAR 타입의 경우, 상세 내용이 500자까지만 작성 가능한 '후기' 테이블에 '내용' 필드는 VARCHAR(500) 형태로 작성한다.

DATETIME vs TIMESTAMP - 생성일시, 수정일시

TIMESTAMP는 시간을 UTC로 저장하기 때문에 TIME ZONE에 따라 그 TIME ZONE에 맞는 시간으로 저장된 시간을 볼 수 있다.
하지만 TIMESTAMP는 2038년1월19일까지밖에 저장할 수 없다는 문제가 있어 DATETIME을 주로 사용한다.

ENUM 데이터타입 vs 참조테이블 - 성별, 등급, 결제 타입, 상품 상태, 주문 상태 등

둘 다 필드에 들어갈 수 있는 값을 제한해주는 역할을 한다.

• 참조 테이블의 예 - 등급 테이블

'유저'는 '등급' 테이블에 존재하지 않는 등급을 가질 수 없다.

ENUM의 장점은 성능 및 간편함이고, 참조테이블의 장점은 유연성이다.

따라서 자주 변경될 수 있으면서 필요한 데이터들을 같이 보관해야 하는 '등급'과 '결제 타입'은 참조 테이블을 따로 만들고, '성별', '상품 상태', '주문 상태' 등은 ENUM 데이터 타입을 이용하는 것이 좋다.

관계 설정

일대일 관계, 일대다 관계, 다대다 관계

A라는 테이블과 B라는 테이블이 있다고 할 때, A라는 테이블의 하나의 레코드가 B라는 테이블의 몇 개의 레코드와 연관될 수 있을 지 생각해보고 그에 따라 일대일 관계, 일대다 관계, 다대다 관계를 설정할 수 있다.

ex.
질문: 한 명의 유저는 몇 개의 후기와 연관될 수 있을까? (=몇 개의 후기를 쓸 수 있을까?)

답변: 최소 0개부터 여러개와 연관될 수 있다. (=안 써도 되고 여러개 쓸 수도 있다)
→ 일대다 관계

다대다 관계 풀어주기

'주문 기록'과 '상품'은 다대다 관계를 가진다. 왜냐하면 주문 기록 하나에는 여러가지 상품이 들어있을 수 있고, 하나의 상품은 여러 주문 기록에 들어있을 수 있기 때문이다.

따라서 다대다 관계는 연결 테이블을 이용해 일대다 다대일 관계로 풀어주어야 한다.
여기에서는 '주문 기록'과 '상품' 테이블을 '주문 상품' 테이블로 연결해준다.

문제점 및 해결방안

'~외 3종'과 같은 상품들은 어떤 구조를 만들어서 처리할 것인가?

문제점
마켓컬리에는 하나의 대표 상품에 여러 상품이 묶여 있는 상품이 존재한다.
현재 '상품' 테이블의 구조로는 해당 기능을 구현할 수 없다.

해결방안
'전시용 상품'이라는 테이블을 새로 만들고, 기존 '상품' 테이블은 '실제 상품'이라는 이름으로 변경한다. 그리고 '전시용 상품'과 '실제 상품'은 일대다 관계를 맺어준다. 유저에게 보이는 상품은 '전시용 상품'에 들어있는 상품들이며, 실제 구매하게 되는 상품은 '실제 상품'에 있는 상품이 된다.

'1년 이상된 적립금은 만료' 기능은 어떻게 구현할 것인가?

문제점
현재 적립금에 관한 데이터는 '유저' 테이블과 '결제 기록' 테이블 안에 있다.
위의 테이블을 이용하여 남아있는 적립금과 적립금 내역은 알 수 있지만 1년 이상 된 적립금은 구분할 수 없다.

해결방안
'적립된 금액 기록' 테이블을 따로 만들고, '적립된 금액', '적립금 잔액', '적립금 만료 시점'을 기록한다.

1년 이상 된 포인트를 만료시키는 것은 다음과 같이 구현할 수 있다.

• 적립금을 사용하면 가장 먼저 적립된 적립금부터 '적립금 잔액'에서 차감하게 된다.
• 매일마다 만료 일시를 넘어간 포인트의 '적립금 잔액'을 0원으로 만들어 준다.
• 유저가 사용 가능한 적립금은 '적립된 금액 기록' 테이블에 있는 모든 레코드에 '적립금 잔액' 값을 더해주면 된다.

부분 환불 기능이 생긴다면 어떻게 구현할 것인가?

문제점
주문 취소가 발생하면 단순히 '주문 기록' 테이블에 '주문 상태' 필드를 주문 취소로 바꿔주면 된다.
취소 금액 또한 '결제 기록' 테이블에서 해당 주문에 맞는 '최종 금액'을 찾아주면 된다.

하지만 부분 환불 기능이 필요한 경우 문제가 복잡해진다.

왜냐하면 부분 환불이 될 경우, 결제한 금액과 환불 금액이 달라지게 되어 위에서 설명한 방법이 통하지 않고, 모든 상품이 환불되는 것이 아니기 때문에 어떤 상품이 실제로 얼마나 팔렸는지 알 수 없기 때문이다.

해결방안
'환불 기록'이라는 테이블과 '환불된 상품'이라는 테이블을 따로 만든다.

이제 주문 취소 또는 환불이 발생하면 '환불 기록' 테이블에 '총 환불 금액'을 기록하면 되고, 그에 따라 취소된 상품은 '환불된 상품'에 기록해주면 된다.

실제로 특정 상품이 팔린 상품의 개수를 확인하고 싶다면 '주문된 상품'에 있는 특정 상품에 '주문 수량'을 모두 더한 후, '환불된 상품' 테이블에서 이 상품에 대한 모든 '환불 수량'을 빼주는 식으로 구현할 수 있다.