알기쉬운 데이터 SQL 계약 테이블과 고객 테이블 조인 시 설계방식

알기쉬운 데이터 SQL   계약 테이블과 고객 테이블 조인 시 설계방식

계약 테이블과 고객 테이블의 N:1 관계는 일반적이며, 한명의 고객이 여러 계약을 가질 수 있다는 점이 있습니다..

데이터베이스 설계와 실제 데이터 조인 시 접근방식에 대해 자세히 설명해드릴게요.

1. 계약 테이블과 고객 테이블 조인 시 설계방식

기본 원칙: 외래 키(Foreign Key)를 통한 조인

가장 일반적이고 올바른 설계는 계약 테이블에 고객 테이블의 기본 키(Primary Key)를 외래 키로 포함하는 것입니다.

• 고객 테이블 (Customers)
  • customer_id (PK, 고객 ID)
  • customer_name
  • customer_email
  • ...
• 계약 테이블 (Contracts)
  • contract_id (PK, 계약 ID)
  • customer_id (FK, 고객 ID)
  • contract_date
  • contract_amount
  • ...

조인 시 customer_id만 조인해도 되는지?

네, 맞습니다. 일반적으로 customer_id (외래 키) 만 사용하여 조인합니다. 계약 ID는 계약 테이블 자체의 기본 키이며, 고객 테이블과는 직접적인 관계를 맺지 않습니다. 고객 테이블과의 관계는 오직 customer_id를 통해서만 연결됩니다.

예시 SQL 쿼리:

고객 이름과 각 고객이 맺은 계약 정보를 함께 가져오는 경우:

codeSQL

 

SELECT
    c.customer_name,
    c.customer_email,
    ct.contract_id,
    ct.contract_date,
    ct.contract_amount
FROM
    Customers c
JOIN
    Contracts ct ON c.customer_id = ct.customer_id;

설계 시 고려사항:

• 인덱스(Index) 생성: 조인 성능을 위해 Contracts 테이블의 customer_id 컬럼에 인덱스를 생성하는 것이 좋습니다. Customers 테이블의 customer_id (PK)에는 자동으로 인덱스가 생성됩니다.
• NULL 허용 여부: 모든 계약이 반드시 고객에게 귀속되어야 한다면 Contracts.customer_id는 NULL을 허용하지 않도록 설정합니다. (일반적으로 그렇습니다.)
• 참조 무결성(Referential Integrity): 외래 키 제약조건을 설정하여 Contracts 테이블에 존재하지 않는 customer_id가 입력되는 것을 방지하고, Customers 테이블에서 참조되는 customer_id를 삭제할 때의 규칙(CASCADE, SET NULL 등)을 정의합니다.

2. 실무에서 계약 테이블과 고객 테이블과의 관계 접근 방식

실무에서는 단순히 조인하는 것을 넘어, 비즈니스 로직과 시스템 아키텍처를 고려하여 관계를 접근합니다.

가. 관계의 명확한 정의 및 제약:

• "한 계약은 반드시 한 고객에게만 귀속된다." 이 명제가 핵심입니다. 데이터베이스의 외래 키 제약조건을 통해 이 관계를 강제해야 합니다.
• "고객이 없으면 계약도 존재할 수 없다." (일반적으로) 따라서 Contracts.customer_id는 NOT NULL로 설정하고, Customers 테이블에 해당 고객이 없으면 계약을 생성할 수 없도록 합니다.

나. 데이터 모델링 및 ERD 활용:

• 데이터 모델링 단계에서 ERD(개체-관계 다이어그램)를 그려 두 테이블 간의 관계(1:N)와 각 테이블의 속성들을 시각적으로 명확히 정의합니다. 이는 개발자, 기획자, 비즈니스 담당자 간의 커뮤니케이션에 매우 중요합니다.

다. 비즈니스 로직 구현:

• 계약 생성 시: 새로운 계약을 생성할 때, 반드시 기존 Customers 테이블에 존재하는 customer_id를 입력받도록 시스템을 설계합니다. 예를 들어, 계약 등록 화면에서 고객 정보를 먼저 선택하거나 검색하여 customer_id를 가져오도록 합니다.
• 고객 삭제 시: 만약 고객을 삭제하려 할 때 해당 고객과 연결된 계약이 존재한다면, 데이터 무결성 유지를 위해 삭제를 막거나, 계약 정보도 함께 삭제(CASCADE DELETE)할 것인지, 아니면 Contracts.customer_id를 NULL로 설정할 것인지 등의 정책을 미리 정의해야 합니다. 일반적으로는 중요 데이터이므로 삭제를 막거나, 소프트 딜리트(Soft Delete: 실제 데이터는 남겨두고 상태값만 '삭제됨'으로 변경)를 사용합니다.
• 보고서 및 분석: 특정 고객의 모든 계약 현황을 보거나, 전체 계약 중 고객별 분포를 분석하는 등의 보고서를 작성할 때 조인이 활발하게 사용됩니다.

라. 성능 고려 (대용량 데이터):

• 조인 성능 최적화: 앞서 언급했듯이 customer_id에 인덱스를 걸어 조인 성능을 향상시킵니다.
• 데이터 웨어하우스/분석 시스템: 실시간 OLTP(온라인 트랜잭션 처리) 시스템에서는 정규화된 테이블을 사용하지만, 대용량 분석을 위한 데이터 웨어하우스나 데이터 마트에서는 비정규화된 형태로 고객 정보와 계약 정보를 미리 조인하여 저장해두는 경우도 있습니다. 이는 분석 쿼리 성능 향상을 위함입니다.
• 캐싱(Caching): 자주 조회되는 고객 정보나 계약 정보는 캐싱하여 데이터베이스 부하를 줄이고 응답 속도를 높일 수 있습니다.

마. 확장성 고려:

• 나중에 계약에 관련된 다른 정보(예: 결제 정보, 상품 정보)가 추가될 때, 이 테이블들이 contract_id를 외래 키로 참조하도록 설계하여 확장성을 유지합니다. 고객 관련 추가 정보(예: 고객 등급, 상담 이력)도 customer_id를 통해 연결될 수 있습니다.

예시 시나리오:

"우리 회사는 신규 고객을 유치하면서 계약을 맺습니다. 이 때 고객 정보와 계약 정보는 어떻게 관리하는 것이 좋을까요?"

1. 고객 가입/등록: 신규 고객이 가입하면 Customers 테이블에 customer_id를 포함한 고객 정보를 먼저 생성합니다.
2. 계약 체결: 고객이 상품/서비스에 대한 계약을 체결하면 Contracts 테이블에 contract_id와 함께, 해당 고객의 customer_id를 외래 키로 저장합니다. 이 때 Customers 테이블에 존재하지 않는 customer_id로는 계약을 생성할 수 없습니다.
3. 데이터 조회: 마케팅 부서에서 "A 고객이 어떤 계약들을 가지고 있는지 알고 싶다"고 요청하면, Customers 테이블과 Contracts 테이블을 customer_id로 조인하여 A 고객의 모든 계약 목록을 보여줍니다.
   위에서 설명한 내용들을 바탕으로 데이터베이스 스키마와 ERD의 예시를 보여드릴게요.
   ERD (Entity-Relationship Diagram) 예시:
   고객 엔티티와 계약 엔티티는 1:N 관계를 가집니다. 즉, 한 명의 고객은 여러 건의 계약을 가질 수 있습니다.

codeMermaid

 

erDiagram
    CUSTOMER {
        VARCHAR(50) customer_id PK "고객 ID"
        VARCHAR(100) customer_name "고객 이름"
        VARCHAR(100) customer_email "고객 이메일"
        VARCHAR(20) customer_phone "고객 전화번호"
        DATE created_at "고객 등록일"
    }

    CONTRACT {
        VARCHAR(50) contract_id PK "계약 ID"
        VARCHAR(50) customer_id FK "고객 ID"
        DATE contract_date "계약 체결일"
        DECIMAL(10,2) contract_amount "계약 금액"
        VARCHAR(50) contract_type "계약 유형"
        DATE start_date "계약 시작일"
        DATE end_date "계약 종료일"
    }

    CUSTOMER ||--o{ CONTRACT : has

테이블 스키마 예시 (PostgreSQL 기준):

codeSQL

 

-- 고객 테이블
CREATE TABLE Customers (
    customer_id VARCHAR(50) PRIMARY KEY, -- 고객을 식별하는 고유 ID
    customer_name VARCHAR(100) NOT NULL, -- 고객 이름
    customer_email VARCHAR(100) UNIQUE,  -- 고객 이메일 (고유해야 할 수 있음)
    customer_phone VARCHAR(20),          -- 고객 전화번호
    created_at DATE DEFAULT CURRENT_DATE -- 고객 등록일
);

-- 계약 테이블
CREATE TABLE Contracts (
    contract_id VARCHAR(50) PRIMARY KEY, -- 계약을 식별하는 고유 ID
    customer_id VARCHAR(50) NOT NULL,    -- 해당 계약을 맺은 고객 ID (외래 키)
    contract_date DATE NOT NULL,         -- 계약 체결일
    contract_amount DECIMAL(10,2) NOT NULL, -- 계약 금액
    contract_type VARCHAR(50),           -- 계약 유형 (예: "보험", "서비스", "렌탈")
    start_date DATE,                     -- 계약 시작일
    end_date DATE,                       -- 계약 종료일
    
    -- 외래 키 제약 조건 설정
    -- Contracts 테이블의 customer_id는 Customers 테이블의 customer_id를 참조함
    -- 고객이 삭제될 때 계약은 어떻게 처리할지 정책 필요 (예: RESTRICT, CASCADE, SET NULL 등)
    -- 여기서는 고객이 삭제될 경우 계약 삭제를 방지 (RESTRICT)
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES Customers (customer_id)
        ON DELETE RESTRICT -- 고객 삭제 시 해당 고객의 계약이 있으면 삭제를 막음
);

-- 조인 성능 향상을 위한 인덱스 생성
CREATE INDEX idx_contracts_customer_id ON Contracts (customer_id);

이러한 설계는 데이터 무결성을 보장하고, 관계형 데이터베이스의 장점을 최대한 활용하면서 비즈니스 요구사항에 유연하게 대응할 수 있는 기반을 제공합니다.