‘BETTER WAY 3. 반복 그룹을 제거하자’에서는 UNION 쿼리를 사용해 반복 그룹을 포함하는 테이블을 ‘정규화’하는 방법을 알려 주었다. 코드 3-2와 같이 뷰를 사용해도 동일한 결과를 얻을 수 있다.

코드 3-2 반복 그룹이 있는 테이블을 정규화하는 뷰

CREATE VIEW vDrawings AS

SELECT a.ID AS DrawingID, a.DrawingNumber

FROM Assignments AS a;

 

CREATE VIEW vPredecessors AS

SELECT 1 AS PredecessorID, a.ID AS DrawingID,

  a.Predecessor_1 AS Predecessor

FROM Assignments AS a

WHERE a.Predecessor_1 IS NOT NULL

UNION

SELECT 2, a.ID, a.Predecessor_2

FROM Assignments AS a

WHERE a.Predecessor_2 IS NOT NULL

UNION

SELECT 3, a.ID, a.Predecessor_3

FROM Assignments AS a

WHERE a.Predecessor_3 IS NOT NULL

UNION

SELECT 4, a.ID, a.Predecessor_4

FROM Assignments AS a

WHERE a.Predecessor_4 IS NOT NULL

UNION

SELECT 5, a.ID, a.Predecessor_5

FROM Assignments AS a

WHERE a.Predecessor_5 IS NOT NULL;

---