[sql-server] 데이터베이스에서 상속을 어떻게 나타낼 수 있습니까?

@Bill Karwin 은 SQL 엔터티-속성-값 안티 패턴 에 대한 솔루션을 제안 할 때 그의 SQL 안티 패턴 책 에서 세 가지 상속 모델을 설명 합니다. 이것은 간단한 개요입니다.

단일 테이블 상속 (일명 계층 당 테이블 상속) :

첫 번째 옵션 에서처럼 단일 테이블을 사용하는 것이 가장 간단한 디자인 일 것입니다. 언급했듯이 하위 유형별 속성은 해당 NULL속성이 적용되지 않는 행에 값을 지정해야합니다. 이 모델을 사용하면 다음과 같은 정책 테이블이 하나 생깁니다.

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

디자인을 단순하게 유지하는 것이 장점이지만이 방법의 주요 문제점은 다음과 같습니다.

• 새로운 하위 유형을 추가 할 때는 이러한 새 객체를 설명하는 속성을 수용하도록 테이블을 변경해야합니다. 하위 유형이 많거나 정기적으로 하위 유형을 추가하려는 경우 문제가 발생할 수 있습니다.

• 어떤 속성이 어떤 하위 유형에 속하는지 정의 할 메타 데이터가 없기 때문에 데이터베이스는 어떤 속성이 적용되고 어떤 속성을 적용하지 않을 수 없습니다.

• 또한 NOT NULL필수이어야하는 하위 유형의 속성을 적용 할 수 없습니다 . 일반적으로 적합하지 않은 응용 프로그램에서이를 처리해야합니다.

콘크리트 테이블 상속 :

상속을 처리하는 또 다른 방법은 각 하위 유형에 대해 새 테이블을 작성하고 각 테이블의 모든 공통 속성을 반복하는 것입니다. 예를 들면 다음과 같습니다.

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+

--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |
+------+---------------------+------------------+

이 설계는 기본적으로 단일 테이블 방법에 대해 식별 된 문제점을 해결합니다.

• 에 필수 속성을 적용 할 수 있습니다 NOT NULL.

• 새 하위 유형을 추가하려면 기존 유형에 열을 추가하는 대신 새 테이블을 추가해야합니다.

• vehicle_reg_no속성 정책 의 필드 와 같은 특정 하위 유형에 대해 부적절한 속성이 설정 될 위험도 없습니다 .

• type단일 테이블 메소드에서와 같이 속성이 필요하지 않습니다 . 유형은 이제 메타 데이터 (테이블 이름)로 정의됩니다.

그러나이 모델에는 몇 가지 단점이 있습니다.

• 공통 속성은 하위 유형별 속성과 혼합되며이를 식별하는 쉬운 방법은 없습니다. 데이터베이스도 알 수 없습니다.

• 테이블을 정의 할 때 각 하위 유형 테이블에 대한 공통 속성을 반복해야합니다. 그것은 확실히 건조 하지 않습니다 .

• 하위 유형에 관계없이 모든 정책을 검색하기가 어려워지고 많은 정책이 필요합니다 UNION.

다음은 유형에 관계없이 모든 정책을 쿼리하는 방법입니다.

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

새 하위 유형을 추가하려면 UNION ALL각 하위 유형마다 추가 로 위의 쿼리를 수정해야하는 방법에 유의하십시오 . 이 작업을 잊어 버린 경우 응용 프로그램에 쉽게 버그가 발생할 수 있습니다.

클래스 테이블 상속 (일명 유형 당 테이블 상속) :

이것은 @David가 다른 답변에서 언급 한 솔루션입니다 . 모든 공통 속성을 포함하는 기본 클래스에 대한 단일 테이블을 작성합니다. 그런 다음 각 하위 유형에 대해 특정 테이블을 작성하고 기본 키는 기본 테이블 의 외래 키 역할도합니다 . 예:

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

이 솔루션은 다른 두 가지 디자인에서 식별 된 문제를 해결합니다.

• 필수 속성은으로 시행 할 수 있습니다 NOT NULL.

• 새 하위 유형을 추가하려면 기존 유형에 열을 추가하는 대신 새 테이블을 추가해야합니다.

• 특정 하위 유형에 부적절한 속성이 설정 될 위험이 없습니다.

• type속성이 필요하지 않습니다 .

• 이제 공통 속성은 더 이상 하위 유형별 속성과 혼합되지 않습니다.

• 마지막으로 DRY를 유지할 수 있습니다. 테이블을 작성할 때 각 하위 유형 테이블에 대해 공통 속성을 반복 할 필요가 없습니다.

• id정책에 대한 자동 증분 관리는 각 하위 유형 테이블을 독립적으로 생성하는 대신 기본 테이블에서 처리 할 수 ​​있기 때문에 쉬워집니다.

• 에 관계없이 지금은 매우 간단하게 하위 유형의 모든 정책을 검색 : 없음 UNION의 필요 – 단지를 SELECT * FROM policies.

클래스 테이블 접근 방식이 대부분의 상황에서 가장 적합하다고 생각합니다.

이 세 가지 모델의 이름은 Martin Fowler의 책 Patterns of Enterprise Application Architecture 에서 왔습니다 .

답변

CREATE TABLE person (
    id int ,
    name varchar(20),
    CONSTRAINT pessoa_pkey PRIMARY KEY (id)
);

CREATE TABLE natural_person (
    social_security_number varchar(11),
    CONSTRAINT pessoaf_pkey PRIMARY KEY (id)
) INHERITS (person);


CREATE TABLE juridical_person (
    tin_number varchar(14),
    CONSTRAINT pessoaj_pkey PRIMARY KEY (id)
) INHERITS (person);