[Effective SQL] 데이터 모델 설계 上

✨ 좋은 관계형 모델 설계하기 

 

1. 모든 테이블에 기본 키가 있는지 확인 

한 테이블에 있는 특정 row와 나머지 row를 구별할 수 있어야 하므로 모든 테이블에는 컬럼 한 개 이상으로 구성된 기본키(PK)가 있어야 한다. 

• PK는 row마다 유일해야하므로 null 값을 가질 수 없음
• 안정적인 값이어야 함(값을 갱신할 필요 X)
• 가능한 한 간단한 형태여야 함 

👉 이런 목표를 달성하는 일반적인 방법 = 의미 없는 숫자 데이터로 자동 생성되는 컬럼 

 

• 참조 무결성을 준수한다 = null이 아닌 외래키(FK)가 설정된 자식 테이블의 각 레코드와 일치하는 레코드가 부모 테이블에 존재함
• 복합 기본키는 사용하지 않는 것이 좋음
  • PK를 정의할 때 대부분의 데이터베이스 시스템은 해당 컬럼에 유일 인덱스를 같이 만든다. 컬럼 두 개 이상에 유일 인덱스를 만들면 데이터베이스 시스템이 할일만 더 많아짐
  • 일반적으로 PK로 조인을 수행하는데, PK가 여러 컬럼으로 구성되어있으면 쿼리가 좀 더 복잡해지고 느려짐

👉 복합 기본키의 장점은 추후에 정리 ..!

 

 

2. 중복으로 저장된 데이터 항목 제거

데이터베이스 정규화의 목표 = 중복 데이터 제거해 데이터를 처리할 때 사용되는 자원 최소화
사용자가 동일한 데이터를 한 군데 이상에서 입력하지 않도록 함

• 중복 데이터를 제거하면 비정상적인 삽입, 갱신, 삭제를 막을 수 있음
• 일관성 없는 데이터 발생을 최소화할 수 있음

 

 

3. 반복 그룹 제거 

반복 그룹을 처리할 때 UNION 쿼리 사용 👉 읽기 전용 뷰를 만들어 데이터 정규화 

 

 

 

4. 컬럼당 하나의 특성만 저장

검색을 하거나 값을 집계할 때 특성 값을 분리하기 어렵기 때문 !

 

• 주소나 전화번호 같은 컬럼의 데이터 일부를 걸러내려면 최소 수준의 데이터 조각으로 분할 
• 보고서나 목록을 뽑으려고 특성들을 재결합할 때는 SQL의 문자열 연결 기능을 사용

 

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

CREATE TABLE Orders(
	OrderNumber int NOT NULL,
    OrderDate date NULL,
    ShipDate date NULL,
    CustomerID int NULL,
    EmployeeID int NULL,
    OrderTotal decimal(15, 2) NULL
);

👉 OrderTotal 컬럼을 두는 것이 좋은 걸까...?

 

• 데이터베이스 성능에 심각한 영향 미침
• 테이블의 row가 변경, 삽입, 삭제될 때마다 값을 재계산해야하므로 데이터 무결성을 유지하기 어려움

---

✴️ 사용 방법

• AS 키워드 다음에 수행할 계산 정의해서 처리 가능

CREATE FUNCTION dbo.getOrderTotal(@orderId int)
RETURNS money
AS 
BEGIN
	DECLARE @r money
    SELECT @r = SUM(Quantity * Price)
    FROM Order_Details WHERE OrderNumber = @orderId
    RETURN @r;
END;
GO

CREATE TABLE Orders(
	OrderNumber int NOT NULL,
    OrderDate date NULL,
    ShipDate date NULL,
    CustomerID int NULL,
    EmployeeID int NULL,
    OrderTotal money AS dbo.getOrderTotal(OrderNumber)
);

• GENERATED 키워드 사용

-- 테이블을 변경하려고 INTEGRITY 옵션을 끔
SET INTEGRITY FOR Order_Details OFF;

-- 표현식을 사용해 계산 컬럼을 생성함
ALTER TABLE Order_Details
	ADD COLUMN ExtendedPrice decimal(15, 2)
    	GENERATED ALWAYS AS (QuantityOrdered * QuotedPrice);

-- INTEGRITY 옵션을 다시 켬
SET INTEGRITY FOR Order_Details
IMMEDIATE CHECKED FORCE GENERATED;

-- 계산 컬럼에 인덱스 생성
CREATE INDEX Order_Details_ExtendedPrice
	ON Order_Details(ExtendedPrice);

CREATE TABLE Order_Details(
	OrderNumber int NOT NULL,
   	ProductNumber int NOT NULL,
   	QuotedPrice decimal(15, 2) DEFAULT 0 NULL,
    QuantityOrdered smallint DEFAULT 0 NULL,
    ExtendedPrice decimal(15, 2)
    	GENERATED ALWAYS AS (QuotedPrice * QuantityOrdered)
);

👉 계산 컬럼에 만든 인덱스를 통해 해결하기도 함

👉 인덱스 적용이 어려울 때는 테이블에 계산 결과를 저장해 놓는 방법 대신 뷰를 이용해 수행할 계산 정의