7강. 관계에 대하여 #2

관계설정 창에서 관계 설정하기

관계 정의할 때 유용하게 쓰이는 단축키

1. F11 - 데이터베이스 창으로 전환합니다.
2. Alt+T R - 관계 창을 엽니다.
3. Alt+R T - 테이블 표시 대화 상자를 엽니다.
4. Alt+R R - 관계 편집 대화 상자를 엽니다.

참조 무결성 설정하기

위에서 언급하였지만 액세스에서 여러 방법으로 관계를 정의할 수 있지만 참조무결성은 관계설정 창을 통해서만 가능합니다.

관계설정 창에서 관계에 포함될 두 테이블과 연결 필드 관계를 정의하고 테이블의 참조 무결성을 설정하게 됩니다. 이 때 쿼리에서 두 테이블을 연결하여 자료를 가져오는 방법인 조인(Join)의 기본값을 미리 설정해 둘 수도 있습니다. 

참조무결성이 설정이 설정되면 필드와 필드를 연결한 관계선 양쪽에 관계의 종루가 일[1]과 다[∞]를 뜻하는 기호로 표시됩니다.

1) 참조무결성 설정
관계 테이블에서 자료를 추가할 때 외래키 값이 기본 테이블(일측의 테이블)에 존재하지 않으면 관계 테이블에 자료를 추가할 수 없습니다.
2) 관련 필드 모두 업데이트 하기
기본 테이블의 기본키 필드를 수정하면 다른 관계 테이블의 외래키 필드의 값을 동시에 수정을 해서 자료의 관계가 끊어지지 않도록 합니다. 따라서 기본 테이블의 기본키 필드의 값을 사용자가 수정 가능한 경우는 이 옵션을 켜둡니다.
3) 관련 레코드 모두 삭제
기본 테이블의 자료를 삭제를 하면 이 기본 테이블과 연결된 관계 테이블의 관련 레코드를 모두 삭제합니다. 이런 경우는 주로 하나의 주 업무를 구성하는 Master-Detail 형식의 관계일 때 설정합니다. 그러나 다측 테이블에서 일측 테이블을 조회하는 관계일 때는 이 옵션을 설정해 두면 예기치 않게 레코드를 잃어버릴 수 있습니다. 뒤에 나오는 'Northwind 예제 파일의 관계 정의 도식 들여다 보기' 에서 설명합니다.

관계를 만들지 않았어도 쿼리에 두 테이블을 추가하고 그 테이블에 데이터 형식이 같거나 호환되는 필드가 있으며 조인 필드 중에서 하나가 기본 키이면 자동으로 조인이 만들어집니다. 
이때 꼭 알아 두셔야 할 것은 참조 무결성이 강화되지 않는다는 것입니다. 따라서 "일(1)"과 "다(∞)" 기호는 표시되지 않습니다.

이렇게 테이블 사이의 관계를 정의해서 자료의 무결성을 유지하게 되고 쿼리, 폼, 보고서에 여러 테이블의 정보를 한 번에 표시할 수 있게 됩니다.

Northwind를 연 다음 액세스의 메뉴에서 도구->관계를 클릭하여 관계 설정 창을 불러내고 그림에 표시된 1, 2, 3, 4 순서로 확인해 보십시오.

조인 유형 설정하기

관계 창에서 관계의 조인 유형을 정의해도 관계 자체에는 영향을 주지 않습니다. 다만 조인 유형의 설정은 질의에서 두 테이블에서 자료를 가져오는 방법으로 기본값을 설정해 둔 것뿐입니다. 따라서 쿼리에서 하고자 하는 작업에 따라 자유롭게 변경할 수 있습니다. 그러므로 관계설정 창에서는 조인의 유형은 신경 쓰지 않아도 됩니다. 기본값은 옵션 1로 내부 조인(Inner Join)입니다.
조인의 역할에 대해서는 나중에 '액세스 질의테이블' 강좌 편에서 설명을 합니다.

Northwind 예제 파일의 관계 정의 도식 들여다 보기

다음은 액세스의 예제 데이터베이스인 Northwind.mdb파일의 관계 정의 도식입니다.
Northwind예제 파일메뉴의 최근 사용 파일 목록에 나타나 있으면 그걸 선택하시고, Northwind 예제 파일이 어디 있는지 모르시면 파일 찾기로 찾아 실행하시면 됩니다. 액세스 XP 버전에서는 도움말 메뉴아래 따로 예제 데이터베이스라는 메뉴가 있습니다.

3가지 형태의 참조무결성에 관련된 관계 정의를 볼 수 있습니다.

기본적으로 모든 테이블의 관계는 참조무결성이 설정되어 있습니다. 

[1]은 '항상 참조 무결성 유지'만 체크된 경우로 주문관리 업무에 보조 정보 테이블들입니다. 일(One)측의 기본 테이블의 고유 코드로 처리된 기본키를 다(Many)측의 관계 테이블에서 참조하는데 이러한 테이블들의 레코드의 기본키는 일련번호 기본키로 사용자가 마음대로 바꿀 수 없습니다. 따라서 '관련 필드를 모두 업데이트' 옵션은 불필요합니다. 
'관련 자료 모두 삭제' 옵션은 어떨까요? 이 테이블들은 주요 업무를 담당하지는 않습니다. 그런데 일괄 삭제를 설정해두면 주요 자료를 예기치 않게 잃어버릴 수 있습니다. 예를 들어서 어떤 제품을 더 이상 취급하지 않는다고 Products 테이블에서 레코드를 삭제하면 OrderDetails 테이블에서 이 제품과 관련된 모든 자료가 순식간에 없어질 겁니다. 따라서 관계 테이블에서 자료가 추가될 때 엉뚱한 값이 들어가지 않도록 무결성만 유지하면 됩니다.

[2]는 주문과 주문고객에 대한 관계인데 고객의 고유 코드는 사용자가 바꿀 수 있는 기본키로 잘못하면 값의 수정이 일어날 수 있으므로 '관련 필드를 모두 업데이트' 옵션을 켜 두어서 기본 테이블의 기본키 값의 수정이 있으면 관계 테이블의 외래키 값이 같이 변경됨으로써 그 관계가 끊어지지 않도록 해야 합니다.

[3]의 경우는 Orders와 OrderDetails 테이블간의 관계인데 기본 테이블인 Orders 테이블의 기본키가 일련번호 기본키로 사용자가 수정을 할 수 없으므로 있으므로 '관련 필드를 모두 업데이트' 옵션을 켜둘 필요는 없습니다. 그러나 주문을 취소하는 경우에 있어서 자료를 삭제할 때 기본 테이블(Orders)의 자료를 삭제하면 관계 테이블(OrderDetails)의 관련 자료도 동시에 삭제되어야 하므로 '관련 자료 모두 삭제' 옵션을 켜두어야 합니다..
물론 기본테이블의 기본키를 사용자가 수정할 수 있도록 필드를 사용하면 '관련 필드를 모두 업데이트'와 '관련 필드를 모두 업데이트' 옵션을 모두 켜두어야 합니다.

초보자의 관계 설정 창 감상하기

관계정의 창을 보면 그 데이터베이스의 운영에 대한 윤곽을 대충 알 수 있습니다. 대개 효율적인 구조를 가진 경우는 관계선이 일목요연 하게 보입니다. 다음은 어느 초보자 분이 질문과 함께 올린 파일의 관계설정 내용입니다. 

아래의 관계창을 감상해 봅시다.

일견에 보면 다음과 같은 생각이 듭니다.
1) 관계 종류표시가 보이지 않는 것으로 보아 참조 무결성 설정에 무성의 합니다.
2) 일단 관계선이 어지러운 것이 테이블의 구조가 웬지 바람직하지 않을 것 같습니다.

이제 자세히 들여다 봅시다.

1. 생지입고의 분류ID 필드가 품목분류 테이블에 중복되어 관계가 정의되어 있습니다. 즉, 제품목록 테이블에만 분류ID필드가 있어도 될 것을 생지입고, 생지출고 테이블에 모두 분류ID가 들어 있다는 것을 관계선만 보고도 알 수 있습니다. 이렇게 정보가 여기저기 중복되어 있으면 유지하기 위해 많은 노력이 필요합니다.. 관계형 데이터베이스의 기본 개념은 중복을 없애는 겁니다(다음에 있을 정규화 강좌에서 자세히 설명됩니다). 제품 정보에 속하므로 제품목록에만 두고 나머지 테이블의 분류ID는 삭제해야 합니다.
   
2. 품목분류 테이블은 분류ID(숫자)와 코드형식의 분류명으로 되어 있습니다. 이 경우 분류명이 코드형태로 작성되어 있고 내용으로 보아 코드만 보아도 의미를 알 수 있을 것 같습니다. 그러나 설명 필드를 만들어 설명을 적어 두시는 것이 바람직합니다. 그리고 그 코드의 설명은 필요할 때 조회해서 확인하면 좋겠습니다. 
   
3. 품목분류 테이블은 여러 테이블에 관계가 설정되어 있습니다. 이러한 분류테이블은 일반 사용자가 마음대로 사용하지 못하게 하고 자료 관리자가 필요에 따라 수정을 할 수 있는데 일괄삭제나 일괄수정이 필요하므로 그 때 관계를 설정하고 수정하도록 하거나 프로그래머가 그 부분을 VBA를 이용하여 처리하도록 하는 것이 좋습니다. 따라서 관계를 설정하지 않도록 합니다.
   
4. 품목에 대한 분류 입력은 품목분류 테이블의 분류ID보다는 분류명이 의미를 가지고 있는 코드 형식이므로 이 값을 입력하는 것이 더 좋겠습니다. 그 코드가 무엇을 뜻하는지 자꾸 질의를 하지 않아도 되고 크기도 크지 않으니까요.
   
5. 제품목록 테이블의 필드 구성은 제품에 대한 정보와 제품의 주문 정보가 섞어져 있습니다. 따라서 [제품목록] 테이블과 [주문정보] 테이블로 분리를 해야 합니다. 
   
6. 제품정보만을 저장할 제품목록 테이블의 자료는 아주 많지 않을 겁니다. 따라서 분류명이 아주 크지 않는 한 코드형태로 넣는 것 보다는 분류명 자체를 저장해두는 것이 효율적이라고 판단됩니다.
   
7. 입고와 출고는 업무에 따라 하나의 테이블로 운영할 수도 있고 따로 운영할 수도 있습니다.
   
8. 생지입고처 테이블에는 입고처 정보가 너무 빈약합니다. 설령 입고처 이름만 필요하더라도 주소, 연락전화 등의 추가 정보를 넣어서 구성하도록 합니다.
   
9. 주 업무용 테이블이 아닌 부수적인 조회테이블은 가능하면 일련번호 기본키를 이용하여 관계 정의시 참조 무결성 설정을 간결히 할 수 있도록 하는 것이 좋습니다.

이상과 같이 관계설정 창은 데이터베이스의 기능과 구조를 일견에 파악할 수 있도록 해주는 기능도 가지고 있습니다. 

관계설정 창을 잘 이용하도록 하십시오.
업무파악이 되면 더 수정보완 해야 할 부분이 있겠지만 일단 원본 파일의 테이블을 그대로 유지해서 정리하여 일단 다음과 같은 모습으로 마무리 합니다. 입출고를 하나의 테이블로 관리할 수 있는 상황이라면 더 간단해 질겁니다.

  목차 | 이전 | 다음