Modifying Foreign Key Definitions

DATABASE/Articles 2014. 4. 21. 16:32

최근 Information Management 스티브 호버만(Steve Hoberman)이 재미있는 글을 하나 올려서 소개합니다.


대충 내용은 ERWin등 모델링 툴에서 부모테이블의 Primary key를 자식테이블에서 Foreign Key로 지정했을때, 

부모테이블 컬럼의 definition을 자식테이블에서

  - 그대로 사용하느냐?

  - 마느냐? 

  - 아니면 재정의 하느냐?

에 대해 여러 모델러, 아키텍트들의 의견 입니다.

보통 모델링을 하면서 이 부분은 크게 문제삼지 않거나, 그냥 넘어가는 경우가 많은데...

이 내용으로 3페이지 분량을 다루고 있는게 흥미롭네요^^


참고로 저는

  1) 초기에는 부모테이블의 Key definition을 정의하고, 상속받을 경우 자식테이블의 Foreign key definition을 수정하지 않습니다.

    - 이 시기에는 부모테이블의 컬럼의 정의가 변경될 경우가 많기 때문

    - Rolename 지정 컬럼 포함

  2) 관계명을 표기합니다.

  3) 모델검토 완료 후 definition 재정의

    - 단일관계 : 재정의가 필요하다고 판단되는 경우에 한해서만 재정의

    - 다중관계 : 무조건 재정의

와같이 작업을 합니다.

여러분은 어떠하신지요?

When we create a one-to-many relationship between two entities, we copy the primary key from the entity on the one side (the parent entity) over as a foreign key to the entity on the many side (the child entity). We traditionally copy over all of the metadata associated with the primary key such as name, format and definition. The one exception where a foreign key can have a different name than its primary key is when there is more than one relationship from the same entity. To avoid having two or more data elements with the same name in the same entity, we "role name," meaning giving the foreign key a different name than its primary key.

For example, in the data model below, in Employee we have a foreign key back to Employee Type which has the same name as its primary key (Employee Type Code), and we have two foreign keys in Customer that point back to Employee. These two foreign keys are role-named to avoid having Employee ID twice in the Customer entity and to provide additional meaning as to what the foreign key represents. Primary Contact Employee ID points back to the Employee who is the primary contact for this Customer, and Initial Contact Employee ID points back to theEmployee who initially made contact with this Customer. 

In this data model, the definition for Employee Type Code is as follows:

Employee Type Code is a numeric value assigned to each organization-wide understood category for an Employee. These codes have business significance and are for human resources internal use only. Examples:

01 = Full time

02 = Part time

03 = Retired

And the definition for Employee ID is:

Employee ID is the unique, mandatory and stable business key for each Employee. It is assigned by human resources and used throughout the organization. Example: Bob Jones is assigned the Employee ID 123-AB-872123

Definitions also copy over from primary key to foreign key, so the Employee Type Code foreign key in Employeehas the same definition as Employee Type Code in Employee Type, and Primary Contact Employee ID andInitial Contact Employee ID have the same definition as Employee ID in Employee.

The Challenge

Should we at times modify the foreign key definitions so they are more relevant to the relationship they represent? After all, if I just see the Employee ID definition in Primary Contact Employee ID, it is not very descriptive.

What guidelines would you apply in deciding whether a foreign key should have a different definition than its primary key?

전문은 아래와 같고, 회원가입을 하셔야 보실 수 있습니다.

'DATABASE > Articles' 카테고리의 다른 글

Define a “Subtype”  (0) 2020.06.05
Can a conceptual data model contain attributes?  (0) 2020.01.23
Define a “Thing”  (0) 2016.02.13
: Comments 0

Write a comment