[DB설계] 27. 카디널리티 검토 참조

표준 SQL 및 데이터베이스 입문

by AI개발자

Mar 1. 2025

ER모델을 데이터베이스의 테이블로 전환하려 할 때, 걸리기 쉬운 부분은 관계(relationship), 즉 엔티티와 엔티티를 연결하는 선부분입니다.

이 조합은 1대다, 다대1, 다대다, 1대1로 정리할 수 있습니다. 이를 하나씩 검토해 봅시다.

ER모델에서는 엔티티 간의 관계에 대한 카디널리티에 제약이 없지만, 관계형 데이터베이스에 매핑할 때는 참조되는 쪽(부모 테이블)은 항상 '1'이어야 합니다. 만약 그렇지 않은 경우에는 조정해야 합니다.

⑴ 1대다의 경우

1대다 관계라면, '캠퍼스'와 '학생'의 관계를 의미합니다. 즉, 하나의 캠퍼스에 여러명의 학생들이 있다는 것입니다. 그 외에도, 전표 데이터의 헤더와 상세정보 또는 제품과 부품 역시 전형적인 1대다 관계입니다.

전표 데이터를 생각해 보면, 한장의 주문서에서 여러개의 품목을 주문할 수 있습니다. 관계형 데이터베이스의 경우, 주문번호, 일자처럼 한장에 한번만 기록되는 항목을 위한 테이블과 품명, 갯수, 금액처럼 한장의 주문서 내에서 여러 번 기록되는 항목(반복항목)을 위한 테이블로 분리합니다. 한장에 한번만 기록되는 테이블은 헤더테이블이라고 하고, 반복항목을 위한 테이블은 상세테이블이라고 부르는 경우가 많습니다.

전표라고 하면, 여러개의 주문을 모아서 한건의 청구서로 만드는 경우도 있습니다. 엔티티의 순서를 생각해 봅시다. 즉, 엔티티 발생 순서를 말합니다. 우선 주문이 있고, 그 다음에 모아서 청구하는 경우라면 이는 1대다 관계가 아니라 다대1 관계라고 볼 수 있습니다.

다대1 관계에 대해서는 나중에 다루기로 하고, 우선 1대다 관계를 살펴봅시다. 1대다 관계는 다시 4가지 패턴으로 나눌 수 있습니다. 이 4가지 패턴은 아래와 같습니다.

① 1대다

② 1대다 (다측이 0일 수 있음)

③ 1대다 (한쪽이 0일 수 있음)

④ 1대다 (둘 다 0일 수 있음)

엔티티와 엔티티의 카디널리티가 1대다인 경우, 다수측의 엔티티에 외부키를 설정합니다. 참조대상은 중복이 불가능(UNIQUE제약)해야 하며, 대부분의 경우 주키(기본키)입니다.

1대다 참조제약

관계형 데이터베이스에서 중복이 가능한 열로부터 다른 테이블을 참조하는 경우, 예를 들어, 학생의 캠퍼스 열이 캠퍼스마스터를 참조하는 경우는 ②의 1대다(다측이 0일 수 있음)관계가 됩니다.

①의 1대다, 즉, '다측이 반드시 1이상이어야 한다'는 것을 관계형 데이터베이스에서 구현하려면, CHECK 제약조건에서 서브쿼리를 사용하거나 ASSERTION(테이블명)을 사용해야 합니다. 이 2가지 방법은 모두 표준 SQL에 규정되어 있지만, 실제로 이를 지원하는 DBMS는 적고, MySQL, MariaDB, PostgreSQL는 미지원합니다. 실제로 이런 규칙은 입력측 프로그램에서 처리할 경우가 많습니다. 예를 들어, 위 그림의 경우, '전표상세는 반드시 1건 이상'이어야 하는데, 입력화면에서 전표번호, 일자, 상세를 동시에 등록하면 당연히 상세는 1건 이상이 됩니다.

문제는 ③과 ④와 같이 '참조 대상이 0일 수 있는' 경우입니다. 현살에서는 있을 수 있는 경우지만, 이를 그대로 모델링해도 되는지 다시 검토할 필요가 있습니다. 예를 들어, 앞서 ③예제인 '오픈시험'의 경우, 학생이 아닌 기록도 관리해야 하는지 검토해야 하고 필요하다면 임시 번호를 부여하여 관리하는 방법을 고려합니다. 또한 ④예제인 '비품이용신청'의 경우, 회사에서 관리되지 않는 물품에 대해서 접수를 일단 보유하고 물품구매가 결정되어 관리용 코드가 부여되면 다른 이용신청과 동일하게 처리하는 방법이나 별도의 신청 경로로 분리하는 경우도 있을 것입니다.

어느 경우든 데이터베이스 구축만을 위해 업무를 불필요하게 늘리지 않도록 주의해야 합니다. 즉, 관리되어야 할 것이 관리되지 않은 상태인지 또는 애초에 데이터베이스 관리가 필요 없는 것인지를 항상 고려해야 합니다. 관계형 데이터베이스에서 '1측'에 0을 허용하려면, '다측'에서 외부키가 설정된 열에 대해 NOT NULL제약을 제거하면 됩니다.

⑵ 1대다는 '부모-자식관계' 또는 '참조관계'를 나타낸다

1대다 관계에 대해 한걸음 더 나아가 봅시다. 1대다 관계에는 2가지 패턴이 있습니다. ER다이어그램을 작성하거나 해석할 때 유용한 팁이 되기 때문에 참고하기 바랍니다.

1대다 관계는 크게 '부모-자식 관계'와 '참조관계'의 2가지로 나눌 수 있습니다. 부모-자식관계란, 먼저 한 테이블(부모테이블)이 존재하고, 그로부터 파생되거나 연관되어 별도의 테이블(자식테이블)이 존재하는 관계를 말합니다. 부모-자신 관계의 경우, 자식테이블의 주키(기본키)에는 부모테이블의 주키가 포함됩니다. 전형적인 부모-자식 관계의 예로서 전표의 헤더(제목)와 상세내역이 있습니다. '한장의 전표엔서 여러 품목을 주문하는' 경우, 품목 부분은 반복항목이 되지만, 이를 관계형 데이터베이스에서 관리할 수 있도록 정규화하면 헤더와 상세내역 2개의 테이블로 분리됩니다. 이때 상세테이브의 주키(기본키)는 헤더테이블에 등록된 주문번호에 상세번호등을 추가한 것으로 구성됩니다.

부모와 자식 관계 (1대다 관계 ①)

한편, 참조관계는 다른 테이블을 참조하는 관계입니다. 테이블1의 주키 항목이 테이블2의 속성항목이 됩니다. 상품마스터와 전표등이 이에 해당합니다.

참조관계 (1대다 관계 ②)

⑶ 다대다의 경우

이제 다대다 관계의 경우를 살펴봅시다. 학원의 경우, 학생과 과목(코스)의 관계가 해당됩니다. 또한, 학생과 동아리의 관계도 하나의 예가 될 수 있고, 같은 품목을 여러 곳에서 구매하여 하나의 납품ㅁ처로부터 여러 품목을 구입하는 경우도 다대다 관계에 해당합니다. '다대다'라는 관계가 존재할 경우, 양쪽을 올바르게 연결하여 참조할 수 있도록 하려면, 이를 '1대다'관계로 조정하고 참조제약을 설정할 필요가 있습니다. 따라서 그 사이에 엔티티를 하나 생성하여 1대다 관계로 조정합니다.

다대다 관계에 엔티티 추가하기

⑷ 다대1의 경우

다음은 다대1 관계에 대해서 알아봅시다. 엔티니를 시간순으로 나열할 때, 다측의 엔티티가 먼저 나오는 경우가 있습니다. 논리적으로는 외부키는 후순위의 엔티티, 즉, '1'측에 있어야 합니다. 예를 들어, 주문이 먼저 있어야 청구가 발생할 수 있기 때문입니다. 하지만 관계형 데이터베이스의 특성상 '1'에서 '다'를 직접 참조할 수는 없기 때문에 외부키는 반드시 '다'측에 있어야 합니다.

그러면 어떻게 해야 할까요?

결국 다대1관계의 경우에도 다대다 관계와 동일하게 엔티티를 하나 더 추가하는 것이 기본입니다. 엔티티를 시간순으로 나열했을 때 다대1 관계가 형성된다면, 1대다 관계와 같은 방식으로 다측에 외부키를 두면 해당 항목이 NULL이 되어버립니다. 따라서 다대1 관계에서도 다대다 관계와 마찬가지로 새로운 엔티티를 추가하여 문제를 해결합니다.

또 다른 방법으로는 다측에 외부키를 두고 NULL을 허용하는 방법도 있지만, NULL을 허용하는 항목은 다루기 어려워지므로 가능한 피하는 것이 안전합니다. 또한 '먼저 발생하는 다측에 외부키를 두는' 것은 '미래를 참조하는' 구조가 되어버려서 원래 데이터에는 있을 수 없는 부자연스러운 구조를 초래할 수 있습니다.

다대1 참조는 NULL이 된다

다대1 경우에도 엔티티 추가한다

위와 같이 됩니다. 다측에 외부키를 둘 때는 이런 변칙적인 구조라도 괜찮은지 충분히 검토해 두어야 합니다.

⑸ 1대1의 경우

마지막으로 1대1 관계에 대해 알아봅시다. 이런 관계는 이해하기 쉽다고 생각할수도 있겠지만, 특히 완벽한 1대1 관계인 경우 왜 그런 구조가 되어 있는지를 고민해야 합니다.

무슨 뜻이냐면, 완벽한 1대1 관계란 양쪽 모두 0(없음)이 아니라 딱 맞게 일치하는 것을 의미합니다. 깔끔해 보이지만, 그럼 어떤 상황이 떠오릅니까? 왜 2개의 엔티티가 별도의 테이블로 분리되었을까요?

예를 들어, 데이터 변환에 사용하는 보조 테이블처럼 편의상 존재하는 경우라면 특별한 문제가 없지만, 데이터 모델 상에서 나타나는 1대1 관계는 서로 다른 코드로 같은 대상을 관리하는 경우가 있습니다. 예를 들어, 본사 'A01'은 공장에서는 'K001'로 불린다면 어느쪽이 어느쪽을 참조하는지 혼란스러울 수 있습니다.

따라서, 1대1 관계일 때는 어느 한쪽에 외부키를 두어 참조로 처리할지, 또는 애초에 별도의 테이블로 분리할 필요가 있는지 신중하게 고민해야 합니다. 만약, 별도로 분리한다면, 다대다 관계처럼 대응시키기 위한 새로운 엔티티를 마련하는 것이 좋을 수도 있습니다.

예를 들어, 앞서 언급한 본사와 공장의 경우라면 A01과 K001의 대응표와 같은 테이블이 될 것입니다. 카디널리티가 1대1인 관계에는 아래와 같은 패턴이 있습니다.

1대1 관계

⑹ 1대1의 의미를 생각하기

먼저 왜 1대1 관계가 형성되어 있는지 생각해 봅시다.

어쩌면 하나의 엔티티, 즉 하나의 테이블로도 충분할 수 있기 때문일수도 있습니다. 엔티티와 엔티티가 1대1인 경우, 가장 중의해야 할 것은 완벽한 1대1인 경우입니다. 우선 왜 두 엔티티가 별도의 테이블로 관리되고 있는지를 확인할 필요가 있습니다. 본래 하나의 테이블에서 관리할 수 있거나, 관리해야 하는데 분리되어 있는 경우가 많으며, 만약 2개의 코드체계가 혼재되어 있다면 왜 그런 상황이 발생했는지도 체크해야 합니다.

한편, 어느 한쪽이 0 또는 1인 경우에는 이는 참조관계이거나 구분코드에 다른 서브타입일 가능성이 있습니다. (서브타입에 대해서는 나중에 따로 다룹니다)

⑺ 1대1 외래키

대상1의 엔티티, 즉, 별도의 테이블로 관리하기로 결정하면 참조방식을 어떻게 할지 고민해 봐야 합니다.

한쪽에 외부키를 두면 되는건가, 아니면 별도의 엔티티를 추가해야 하는건가하는 문제입니다. 조금은 익숙해셨나요?

엔티티와 엔티티가 1대1 관계인 경우에는 어느 한쪽에 외부키를 설정합니다. 이때, 시간순서상 늦게 발생하는 쪽에 외부키를 배치하는 것이 기본입니다. 또한, '1'대'0' 또는 '1'의 경우에는 '0 또는 1'쪽에 외부키를 둡니다. 이는 '1'측의 테이블이 주체이고 '0 또는 1'측의 테이블이 종속된다고 판단되기 때문입니다. 예를 들어, 직원과 직원이 1대만 신청할 수 있는 통근용 자동차를 관리하는 경우, 직원 테이블과 차량 테이블로 관리하는데, 이때 차량 테이블쪽에 외부키를 둡니다.

외래키는 발생이 느린쪽에 넣는다

양쪽 엔티티가 서로 독립적으로 존재하는 경우, 서로 대응하기 위해 별도의 엔티티를 마련하는 것이 적합한 경우가 있습니다. 예를 들어, 독신기숙사(주키는 방번호)와 직원(주키는 직원번호)은 방과 직원간에 '1대1' 관계가 있으나, 각각 별도로 관리되는 엔티티이기 때문에 어느 한쪽도 0이 될 수 있습니다.

따라서, 방번호와 직원번호를 외부키로 하는 새로운 엔티티를 하나 만듭니다. 새로운 엔티티는 '방번호와 직원번호의 조합', '방번호', '직원번호'가 각각 후보키가 됩니다. 이 경우, '방번호와 직원번호의 조합'을 주키로 설정하고 '방번호'열은 NOT NULL 및 UNIQUE, 그리고 '직원번호'열도 NOT NULL 및 UNIQUE로 지정합니다.

독립적인 1대1 관계에 엔티티 추가하기

참조에도 여러가지 의미가 있습니다. 엔티티가 늘어나면 혼란스럽게 생각할 수 있지만, 각각 깔끔해지고 전체 구성도 쉽게 파악할 수 있습니다. 이 부분을 꼼꼼하게 검토해두면 이후 시스템 구축도 훨씬 수월해집니다. 데이터 모델을 작성함으로써, 어떤 업무가 있는지도 명확히 알 수 있습니다.