서브타입의 물리모델 변환 - (2)

1. 슈퍼타입 엔터티가 상위 엔터티인 서브타입 모델

 

2. 서브타입 엔터티가 상위 엔터티인 서브타입 모델

 

2-1 배타 관계를 가지는 서브타입 모델

고객 엔터티는 개인고객과 법인고객의 공통속성을 포함하는 슈퍼타입이다. 
개인고객의 주민등록번호와 법인고객의 법인등록번호의 의미를 통합한 고객고유번호라는 속성을 사용한다.
자연키인 주민등록번호, 법인등록번호는 가능한 식별자로 사용하지 않는 것이 좋다.
고객유형코드, 고객고유번호가 업무 식별자이다. 다른 엔터티가 참조할 가능성이 크므로 참조를 편하게 하기 위해 고객번호라는 인조 식별자를 사용한다.

CREATE TABLE 개인고객(
	  주민등록번호 VARCHAR(14) PRIMARY KEY 
	, 생년월일 DATE
	, 성별 VARCHAR(1) CHECK (성별 IN ('F', 'M'))
	, 결혼여부 VARCHAR(1) CHECK (결혼여부 IN ('O', 'X'))
);

CREATE TABLE 법인고객(
	  법인등록번호 VARCHAR(14) PRIMARY KEY 
	, 대표자명 VARCHAR(5)
	, 설립일자 DATE 
);

CREATE TABLE 고객 (
	  고객번호 BIGINT PRIMARY KEY AUTO_INCREMENT 
	, 고객명 VARCHAR(5) NOT NULL 
	, 고객구분코드 VARCHAR(2) NOT NULL
	, 고객고유번호 VARCHAR(14) NOT NULL UNIQUE 
);

INSERT INTO 개인고객
   SET 주민등록번호 = '901010-1770001';

INSERT INTO 고객
   SET 고객명 = '개인고객1'
     , 고객구분코드 = '개인'
     , 고객고유번호 = '901010-1770001';

INSERT INTO 법인고객
   SET 법인등록번호 = '130111-0006246';

INSERT INTO 고객
   SET 고객명 = '삼성전자'
     , 고객구분코드 = '법인'
     , 고객고유번호 = '130111-0006246';

 

백엔드 서버에서 사용할 때는 아래와 같은 쿼리를 사용하면 된다.

SELECT c.고객번호, c.`고객명`, p.`주민등록번호`, p.`생년월일`, p.`성별`, p.`결혼여부`
, r.`법인등록번호`, r.`대표자명`, r.`설립일자`
FROM 고객 c
LEFT JOIN 개인고객 p
ON c.`고객고유번호` = p.`주민등록번호`
LEFT JOIN 법인고객 r
ON c.`고객고유번호` = r.`법인등록번호`
WHERE c.`고객번호` = 1;

 

2-2 주 식별자가 동일한 배타 관계 모델

개인고객, 법인고객의 주 식별자를 통일하였다. 개인고객 엔터티에서 인스턴스가 생성될 때 법인고객이 이미 사용한 고객번호를 사용하면 안 되기 때문에 해결할 방법이 필요하다. 반대도 마찬가지이다. (본 예제에서는 트리거로 해결)

CREATE TABLE 개인고객2(
    고객번호 BIGINT PRIMARY KEY 
  , 주민등록번호 VARCHAR(14) NOT NULL UNIQUE 
  , 생년월일 DATE
  , 성별 VARCHAR(1) CHECK (성별 IN ('F', 'M'))
  , 결혼여부 VARCHAR(1) CHECK (결혼여부 IN ('O', 'X'))
);

CREATE TABLE 법인고객2(
    고객번호 BIGINT PRIMARY KEY 
  , 법인등록번호 VARCHAR(14) NOT NULL UNIQUE 
  , 대표자명 VARCHAR(5)
  , 설립일자 DATE 
);

CREATE TABLE 고객2 (
    고객번호 BIGINT PRIMARY KEY 
  , 고객명 VARCHAR(5) NOT NULL 
  , 고객구분코드 VARCHAR(2) NOT NULL 
);

 

개인고객 (insert, update) 트리거 생성

DROP TRIGGER if EXISTS tg_insert_개인고객_고객번호2; 
DELIMITER $$

CREATE TRIGGER tg_insert_개인고객_고객번호2 
BEFORE INSERT ON 개인고객2
FOR EACH ROW 
BEGIN 
    DECLARE num INT;
	SET num = 0;

	SELECT COUNT(1) INTO num FROM 법인고객2 WHERE 고객번호 = NEW.고객번호;
	
	if num != 0 then 
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '법인 고객2에 존재하는 중복된 고객번호입니다.';
	END if;
	
END; $$
DELIMITER ;

DROP TRIGGER if EXISTS tg_update_개인고객_고객번호2; 
DELIMITER $$

CREATE TRIGGER tg_update_개인고객_고객번호2 
BEFORE UPDATE ON 개인고객2
FOR EACH ROW 
BEGIN 
	DECLARE num INT;
	SET num = 0;	

	SELECT COUNT(1) INTO num FROM 법인고객2 WHERE 고객번호 = NEW.고객번호;
	
	if num != 0 then 
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '법인 고객2에 존재하는 중복된 고객번호입니다.';
	END if;
	
END; $$
DELIMITER ;

 

법인고객 (insert, update) 트리거 생성

DROP TRIGGER if EXISTS tg_insert_법인고객_고객번호2; 
DELIMITER $$

CREATE TRIGGER tg_insert_법인고객_고객번호2
BEFORE INSERT ON 법인고객2
FOR EACH ROW 
BEGIN 
	DECLARE num INT;
	SET num = 0;	

	SELECT COUNT(1) INTO num FROM 개인고객2 WHERE 고객번호 = NEW.고객번호;
	
	if num != 0 then 
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '개인 고객2에 존재하는 중복된 고객번호입니다.';
	END if;
	
END; $$
DELIMITER ;


DROP TRIGGER if EXISTS tg_update_법인고객_고객번호2; 
DELIMITER $$

CREATE TRIGGER tg_update_법인고객_고객번호2
BEFORE update ON 법인고객2
FOR EACH ROW 
BEGIN 
	DECLARE num INT;
	SET num = 0;	

	SELECT COUNT(1) INTO num FROM 개인고객2 WHERE 고객번호 = NEW.고객번호;
	
	if num != 0 then 
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '개인 고객2에 존재하는 중복된 고객번호입니다.';
	END if;
	
END; $$
DELIMITER ;

 

기초자료 입력

INSERT INTO 개인고객2
   SET 고객번호 = 1
     , 주민등록번호 = '901010-1770001';

INSERT INTO 고객2
   SET 고객번호 = 1
     , 고객명 = '개인고객1'
     , 고객구분코드 = '개인';

INSERT INTO 법인고객2
   SET 고객번호 = 2
     , 법인등록번호 = '130111-0006246';

INSERT INTO 고객2
   SET 고객번호 = 2
     , 고객명 = '삼성전자'
     , 고객구분코드 = '법인';

 

트리거로 인해 에러 발생

INSERT INTO 개인고객2
   SET 고객번호 = 2
     , 주민등록번호 = '901011-1770002';

UPDATE 개인고객2
   SET 고객번호 = 2
 WHERE 고객번호 = 1; 

INSERT INTO 법인고객2
   SET 고객번호 = 1
     , 법인등록번호 = '130111-0006247';

UPDATE 법인고객2
   SET 고객번호 = 1
 WHERE 고객번호 = 2;

 

중복되지 않은 고객번호는 insert 성공

INSERT INTO 개인고객2
   SET 고객번호 = 3
     , 주민등록번호 = '901010-1770002';

INSERT INTO 고객2
   SET 고객번호 = 3
     , 고객명 = '개인고객2'
     , 고객구분코드 = '개인';

 

2-3 배타 속성을 사용하지 않는 배타 관계 모델

고객의 주민등록번호(FK), 법인등록번호(FK) null 허용, 둘 중 하나의 값만 존재해야 한다. 고객의 유형이 추가되면 고객 테이블에 컬럼이 추가되어야 한다. 조회 요건을 복잡하게 만들어서 실무에서 잘 사용되지 않는다.

CREATE TABLE 개인고객(
    주민등록번호 VARCHAR(14) PRIMARY KEY
  , 생년월일 DATE
  , 성별 VARCHAR(1) CHECK (성별 IN ('F', 'M'))
  , 결혼여부 VARCHAR(1) CHECK (결혼여부 IN ('O', 'X'))
);

CREATE TABLE 법인고객(
    법인등록번호 VARCHAR(14) PRIMARY KEY 
  , 대표자명 VARCHAR(5)
  , 설립일자 DATE 
);

CREATE TABLE 고객 (
    고객번호 BIGINT PRIMARY KEY 
  , 고객명 VARCHAR(5) NOT NULL 
  , 고객구분코드 VARCHAR(2) NOT NULL 
  , 주민등록번호 VARCHAR(14)  
  , 법인등록번호 VARCHAR(14) 
);

ALTER TABLE 고객 ADD CONSTRAINT FK_고객_주민등록번호 
FOREIGN KEY (주민등록번호) REFERENCES 개인고객(주민등록번호);

ALTER TABLE 고객 ADD CONSTRAINT FK_고객_법인등록번호 
FOREIGN KEY (법인등록번호) REFERENCES 법인고객(법인등록번호);

/* 
	둘 중 하나만 반드시 존재해야 한다는 배타적 관계를 데이터베이스 측면에서 만족시키기 위해 
	아래와 같은 함수기반 인덱스를 생성할 수 있다. 그래야 업무 규칙도 만족시키고 인덱스 수도 
	최소화 할 수 있다. 
*/
CREATE UNIQUE INDEX IX_고객 ON 고객(
	(case when 고객구분코드='개인' then 주민등록번호 
	      when 고객구분코드='법인' then 법인등록번호 end)
);