SQL

1. SQL 데이터 모델 설계 - Effective SQL

Hvvvi 2021. 5. 21. 13:03
728x90

Effective SQL

- 암퇘지 귀로는 비단주머니를 만들수 없다 ("CAN'T MAKE A SILK PURSE OUT OF A SOW'S EAR")

 

1.1 모든 테이블에 기본키가 있는지 확인하자

기본키(primaryKey) 설정

  • unique, notnull, 안정적인 값[갱신 필요X], 간단한 형태[소수보단 정수]

참조무결성  

  • 기본 키와 참조 키 간의 관계가 항상 유지됨
  • 참조되는 테이블의 행은 참조키가 존재하는 한 삭제될 수 없고, 기본키도 변경될 수 없음

복합키(compoundPrimaryKey) 지양

  • 기본키 지정시 DB는 해당 칼럼에 유일 index를 생성함.
  • 한 칼럼에 유일 index가 여래개이면 DB의 일만 늘어남, 비효율적
  • 일반적으로 기본키를 통해 join을 수행 하는데 기본키가 여려개이면, 쿼리가 복잡해지고 속도가 저하됨.
  • but 제품_id, 제품_판매처_id 처럼 복합키가 더 효율적인 경우도 있음.
  •  

1.2 중복으로 저장된 데이터 항목을 제거하자

- 정규화 -

데이터를 중복으로 저장하면 

  1. 일관되지 않은 데이터
  2. 비정상적인 삽입, 갱신, 삭제 처리
  3. 디스크 공간 낭비 등의 문제를 일으킴.

정규화는 중복데이터 제거, 정보를 주제별로 분할하는 프로세스이다.

적절히 분할되지 않으면

  1. 반복된 값을 입력하게됨
  2. 업데이트, 수정시 입력량이 많아짐
  3. 디스크, 메모리, 네트워크 리소스, 시간 낭비
  4. 비정상적 수행이 날 확률이 높아진다.

정규화의 목표는 중복데이터 제거를 통해 데이터 처리시 자원을 최소화 하는 것.

 

1.3 반복그룹을 제거하자

비용 측면에서 컬럼은 비싸고 로우는 싸다.

반복 그룹 이란?

  • 비슷한 내용을 담기 위하여 여러개의 컬럼을 두는 것.
  • ex) 컬럼에 5개의 어트리뷰트를 저장하기 위하여, attr1, ...., attr5까지 선언하여 저장하는 것.
  • foregin key를 통해 테이블 분리해야함.
  • += 설계가 유연해지고, 데이터 중복을 피할 수 있음

정규화의 목표는 데이터 반복그룹을 제거하고 스키마 변경을 최소화 하는 것.

 

1.4 컬럼당 하나의 특성만 저장하자

- 제 1 정규화 (atomicity) - [1NF]

각 데이터는 원자성을 가져야한다.

  • 이름, 성 분리
  • 주소 지번, 도시, 국가 분리
  • 전화번호 자릿수 분리
  • 개별검색, 그루핑 용이
  • 테이블설계시 개별 특성을 자체 컬럼에 할당한다.
  • 한컬럼에 여러특성이 포함되어 있으면 그루핑이 어렵다.
  • 추출시에는 sql의 연결기능 활용

 

1.5 왜 계산 데이터를 저장하면 좋지 않은지 이해하자

계산필드는 운영중인 DB의 성능에 심각한 영향을 미칠 수 있다.

  • 변경, 삽입, 삭제할 때 마다 다시 계산됨

방법 1. 계산이 사용되는 테이블에 트리거 추가

  • but 작성이 어렵고 비용이 비쌈
  • 트리거: 대상테이블에 변경, 삽입, 삭제시 수행되는 코드

방법 2. 계산 칼럼 정의

    • DB2, SQL server, 오라클에서는 generated를 통해 계산컬럼을 만들수 있다. 
    • 1
      2
      3
      4
      Create the calculated column using an expression
      ALTER TABLE Order_Details
        ADD ExtendedPrice decimal (15, 2)
        GENERATED ALWAYS AS (QuantityOrdered * QuotedPrice);
      		 cs
    • 그러나 성능을 고려해야하며, 비결정적 함수인지도 고려해야함

방법 3. 인덱스 적용이 어려울경우 view를 생성해 select시 계산하는 것이 좋다.

 

1.6 참조 무결성을 보호하려면 외래키를 정의하자

데이터 베이스 스키마 설계시 관련된 부모 테이블의 기본키를 포함하도록 테이블에 외래키를 정의하는 것이 좋다.

  1. 쿼리 디자인 도구 사용이 용이함.
    • join절을 올바르게 만들 수 있도록 도와줌
  2. 일대다 관계에서 일에 해당하는 테이블의 무결성을 강화 하는데 도움을 준다.
    • 명시적으로 외래키를 만들면 부모 테이블에 없는 로우에 대한 자식 테이블의 로우가 없음을 보장

 

1.7 테이블 간 관계를 명확히 하자

- 제 2 정규화 - [2NF]

부분 함수적 종속 제거

- 제 3 정규화 - [3NF]

이행 함수적 종속 제거

 

테이블간 관계를 고려하여 정규화

728x90