교차 테이블을 만드는 대신 nullable 외래 키를 사용할 때의 단점

두 모델은 서로 다른 관계를 나타냅니다.

조인 테이블 사용 , 당신은 다 대다 관계를 모델링하고 있습니다.

간단한 외래 키를 사용하여 일대 다 관계를 모델링합니다.

널링 가능한 외래의 단점 핵심은 다 대다 관계를 모델링 할 수 없다는 것입니다. 이것이 달성하려는 경우입니다.

질문에 대한 편집을 기반으로 학생 테이블을 효과적으로 분할합니다. 같은 키를 가진 두 개의 테이블로. 나는 일반적으로 필드가 너무 많은 테이블에서 이것을 볼 수 있으므로 누군가가 더 관리하기 쉽도록 두 개로 나눕니다 (저는 이것을 돼지에 립스틱 바르기라고 부릅니다).

학생 테이블을 분할하여 만드는 것입니다. 두 번째 테이블에는 레코드가 두 번째 테이블에있을 필요가 없기 때문에 선택적입니다. 이는 null 일 수 있기 때문에 설정할 필요가없는 필드와 매우 유사합니다.

일대 다 관계를 원한다면 단일 테이블을 사용하고 학교 ID를 허용하는 것이 훨씬 좋습니다. 학생 테이블에서 null이됩니다. 외래 키의 경우에도 필드에서 null을 피할 이유가 없습니다. 이는 외부 관계가 선택 사항이라는 것을 의미합니다. 개발자와 DBA는이를 명확하게 이해하고 기본 데이터베이스 엔진이 확실히 잘 작동해야합니다.

조인에 대해 걱정할 필요가 없습니다. 조인이 널 필드와 작동하는 방식에 대한 잘 정의 된 의미가 있습니다. 단일 테이블을 사용하면 3 개가 아닌 2 개의 테이블을 결합 할 수 있습니다.

댓글

• 따라서 일대 다 관계를 모델링하는 경우 (선택적으로 : 1 측에 참여), NULL 값을 가질 수 있다는 사실에도 불구하고 외래 키를 사용해야합니까?
• @Tom 예, 그렇습니다. 정확히 모델링하는 방법입니다. 기술적으로는 조인 테이블을 사용할 수 있지만 데이터 모델은 다대 다를 허용하므로이를 방지하려면 트리거와 데이터베이스 논리가 필요합니다. 잘못된 데이터를 추가 할 수 없도록 관계를 제한하는 것이 좋습니다.
• 제 질문을 수정했습니다.나는 student_id를 School_has_Student 테이블에서 기본 키로 만 만들어 관계를 일대 다로 유지했습니다. 이 방법은 외래 키를 사용하는 것보다 어떤 단점이 있습니까?
• @Tom 내 답변을 수정했습니다.

위에 댓글을 작성했습니다.

“Fundamentals of Database Systems”[…] 책에는 [.. .] 외래 키 열에 NULL 값이 많은 경우 교차 테이블을 사용하는 것이 좋습니다 (예 : 직원의 98 %가 부서를 관리하지 않는 경우)

외래 키 열에 NULL 값이 많으면 프로그램이 처리하는 각 레코드에 대해 대부분 비어있는 열을 처리해야합니다. 열은 아마도 디스크 공간을 차지할 것입니다. 모든 경우의 98 %가 비어 있더라도 관계를 쿼리하는 것은 더 많은 네트워크 트래픽을 제공하는 해당 열을 쿼리하는 것을 의미하며 테이블에서 클래스를 생성하는 ORM을 사용하는 경우 프로그램도 클라이언트에서 더 많은 공간을 필요로합니다. 필요 이상으로. ection 테이블은 이것을 피하고, 동등한 외래 키가 그렇지 않으면 NULL이 아닐 때 필요한 링크 레코드 만있을 것입니다.

반대로, NULL 값이 적지 않은 경우 50 % 이상이라고 가정 해 보겠습니다. 관계는 NULL이 아닙니다. 교차 테이블을 사용하면 디스크 공간이 늘어나고 복잡도가 높아져 네트워크 트래픽이 늘어납니다.

따라서 교차 테이블을 사용하는 것은 최적화의 한 형태 일뿐입니다. 특정한 경우, 특히 요즘에는 디스크 공간과 메모리가 더 저렴 해졌고 훨씬 덜 필요합니다. “Fundamentals of Database Systems”는 원래 20 년 이상 전에 작성되었으며 (1994 년의 두 번째 버전에 대한 참조를 찾았습니다) 그 당시에는 이미 권장 사항이 있었던 것 같습니다. 1994 년 이전에는 대용량 저장소가 여전히 더 비싸고 컴퓨터와 네트워크가 오늘날보다 훨씬 느리기 때문에 공간 최적화가 오늘날보다 훨씬 더 중요했을 것입니다.

위의 진술은 “데이터베이스 시스템의 기초”의 저자가 자신의 권장 사항을 염두에두고 무엇을 염두에 두 었는지 예상하려는 것입니다. 그는 대부분의 시스템에 유효한 대략적이고 일반적인 진술을 한 것 같습니다. 일부 데이터베이스에는 교차 테이블을 훨씬 더 쓸모 없게 만드는 “스파 스 컬럼”과 같은 다른 가능한 최적화가 있습니다.

그러니 그 권장 사항을 잘못 이해하지 마십시오. 책은 말하지 않습니다. 일반적으로 {0,1}:n 관계에 대해 교차 테이블을 선호하거나, 작성한대로 이것이 “올바른 방법”이라는 것을 선호합니다. 다음과 같은 최적화를 사용하면 다음과 같은 경우에만 프로그램이 더 복잡해집니다. 정말 필요합니다.

댓글

• 당신은 ' 특히 OP를 고려하면 ' 특정 항목을 언급하지 않았습니다. ' 데이터베이스가 사용하기에 충분히 스마트 할 가능성이 높습니다. 스파 스 열을위한 작은 공간 만 제공합니다.
• @gardenhead : 이것이 " 가능성이 더 높다고 생각하게하는 이유 "?
• 데이터베이스가 수십 년 동안 사용되어 왔으며 대부분의 인프라의 핵심 구성 요소이기 때문에 고도로 최적화되었습니다.
• @gardenhead : 저보다 부당한 가정을 많이하고있는 것 같습니다. 그럼에도 불구하고 내 수정 사항을 참조하십시오.

개념적 모델은 다음과 같이 표시됩니다. 매우 이례적인 :

물리적 모델은 다음과 같이 표시됩니다. 혼란스러운 더 적은 것을 말합니다 (사람들은 자세히 보지 않으면”M : M “이라고 생각할 것입니다) :

제안 :

대부분의 학생들에게 적용되지 않는 많은 열 (FK 또는 기타)이있는 경우 테이블을 1 : 1 관계가있는 역할 테이블로 분리합니다. 그러나 FK이기 때문이 아닙니다. 열이 대부분의 행에 적용되지 않기 때문입니다.

그렇지 않으면 , nullable FK는 데이터베이스의 일반적인 부분이며 조인 테이블은 일반적으로 M : M 관계 용입니다.

1 : 1 관계의 일반적인 용도는 엔터티가 특정 유형 인 경우에만 적용되는 열이있는 역할 테이블과 성능 또는 스토리지 고려 사항을 위해 BLOB 열을 추출하는 것입니다. FK에서 null 값을 거부하는 것은 일반적인 용도가 아닙니다.

다른 답변 외에도 외래 키의 null 값이 모호하다는 점을 지적하고 싶습니다. 의미 :

1) 학생의 학교 (있는 경우)를 알 수 없습니다 ( “null”의 표준 의미-값을 알 수 없음)

2) 학생이 학교를 가지고 있는지 여부를 알고 있지만 학교가 없습니다

Null의 표준 의미를 사용하는 경우 외래 키 모델에서 “학생은 학교가 없습니다”를 어떻게 표현할 수 있습니까?이 경우, 학교 테이블에 자신의 ID가있는 “학교 없음”항목을 만들어야 할 것입니다. (이상적이지 않음)

댓글

• " Fundamentals of Database Systems " 책에서는 NULL는 다음을 의미 할 수 있습니다. 1) 알 수없는 값. 2) 사용할 수 없거나 보류 된 값. 3) 적용 할 수없는 속성 (이 해석은 NULL 외래 키).
• 그 ' 유용한 목록이지만 null (또는 실제로 모든 값)의 의미는 사용자가 정의 할 수 있습니다.그 목록에 국한되지 않고 디자이너가 말하는 모든 것을 의미 할 수 있습니다. 문제는 두 개 이상이 필요할 때 (또는 의도하지 않게 저장 될 때) 다른 의미를 구별하는 방법입니다.
• 그러면 nullable 외래 키를 사용하는 대신 교차 테이블을 만들어야한다고 제안하는 건가요?
• @Tom 예,이 경우가 더 낫다고 생각합니다.
• @BradThomas-교차 테이블을 사용할 때 동일한 모호성을 피하기 위해 사례 2를 표현 하시겠습니까 (학생이 학교가 없음) School_ID가 NULL 인 교차 테이블의 레코드에 의해?

데이터베이스 테이블에 제약이라는 좋은 점입니다. 따라서 각 학생 중 한 명만 테이블에 나타나지만 그 테이블에는 많은 학교가 표시되는 교차 테이블을 만드는 것은 매우 쉽습니다. 이론을 효과적으로 제공하는 것은 좋지만 결국에는 당신이 묻는 질문에 따라 데이터베이스를 모델링 할 것입니다.

“어떤 학생이 우리 학교에 있는지”라는 질문으로 자주 질문하고 싶다면 정말로 전체 students 테이블을 쿼리하고 싶습니까? 쉬운 교차 테이블.

데이터베이스 : 질문에 최적화

거기 세 번째 테이블을 사용하는 것이 실제로 합리적 일 수있는 사용 사례입니다. 이 예는 순전히 가설적인 것처럼 보일 수 있지만 제 요점을 잘 보여주기를 바랍니다. students 테이블에 더 많은 열을 추가하고 어떤 시점에서 여러 열에 대한 복합 색인을 통해 레코드에 고유성을 적용하기로 결정했다고 가정 해 보겠습니다. “school_id 열도 포함해야합니다. 여기에 문제가 생기기 시작합니다. SQL 설계 방식으로 인해 는 NULL 가능합니다. 기술적 인 관점에서는 완벽하게 이해되지만 직관에 반하여 예상치 못한 결과를 초래할 수 있습니다. 반면에 교차 테이블은 쉽습니다.

최근에 고유성 제약 조건에 대한 요구 사항이 타임 스탬프 열로 인한 “선택적”관계를 모델링해야했습니다. 테이블에 nullable 외래 키를 남겨두면 갑자기 동일한 타임 스탬프를 가진 레코드를 삽입 할 수 있습니다 (감사 / 승인되지 않은 레코드에 설정된 “기본 값”이라고 가정합시다. 아직 oved)-유일한 방법은 nullable 열을 제거하는 것입니다.

보시다시피 “상당히 구체적인 경우이며 다른 사람들이 언급했듯이 대부분의 경우” NULL 값. 모델의 특정 요구 사항에 따라 다릅니다.

이미 제출 된 많은 좋은 제안 외에도 개인적으로 저는 “꼭 필요한 경우가 아니면 외래 키의 팬이 아닙니다. 먼저 참조하는 M : M 관계가 있습니다. 또한 외래 키를 호출하여 해당 테이블 데이터를 쿼리로 가져 오면 더 복잡해지고 테이블 크기, 성능 저하. 다른 사람들이 말했듯이 nullable FK 필드는 지원되지 않을 수 있으며 데이터 무결성 문제를 일으킬 수 있습니다.

학생 학교가 알려지지 않거나 비어있는 상태를 정의하는 경우 NULL (다시 데이터 무결성으로 돌아갑니다.) Tulains의 역할 테이블 제안은 우아하고 null 값을 깔끔하게 허용합니다.