prosource

대리 키 대 자연 키/비즈니스 키

probook 2023. 11. 4. 10:53

대리 키 대 자연 키/비즈니스 키

다시 시작합니다. 오래된 논쟁은 아직도 발생합니다.

비즈니스 키를 기본 키로 사용하는 것이 나을까요, 아니면 비즈니스 키 필드에 고유한 제약 조건이 있는 대리 ID(즉, SQL Server ID)를 사용하는 것이 나을까요?

당신의 이론을 뒷받침할 수 있는 예나 근거를 제시해 주십시오.

대리 키를 사용하는 몇 가지 이유:

안정성:비즈니스 또는 자연스러운 필요로 인해 키를 변경하면 관련 테이블에 부정적인 영향을 미칩니다.값에 연결된 의미가 없기 때문에 대리 키를 변경해야 하는 경우는 거의 없습니다.
규약:PK에 대해 다양한 이름을 가진 테이블을 조인하는 방법을 생각할 필요 없이 표준화된 기본 키 열 이름 지정 규칙을 가질 수 있습니다.
속도: PK 값과 유형에 따라 정수의 대리 키는 더 작고 색인 및 검색 속도가 빠릅니다.

둘 다. 케이크 드시고 드세요.

기본 키에 특별한 표시가 있는 것 외에는 특별한 것은 기본 키에 라벨이 붙어 있다는 것입니다.NOT NULL UNIQUE 제약 조건에 지나지 않으며 테이블은 하나 이상을 가질 수 있습니다.

대리 키를 사용하는 경우에도 비즈니스 규칙에 따라 고유성을 보장하는 비즈니스 키를 원할 수 있습니다.

아직 아무도 대리인이 아닌 열쇠를 지지하는 말을 하지 않은 것 같습니다.자, 그럼...

대리 키의 단점은 의미가 없다는 것입니다(일부에서는 장점으로 언급하지만...).이로 인해 쿼리에 실제로 필요한 것보다 훨씬 더 많은 테이블을 추가해야 하는 경우가 있습니다.비교:

select sum(t.hours)
from timesheets t
where t.dept_code = 'HR'
and t.status = 'VALID'
and t.project_code = 'MYPROJECT'
and t.task = 'BUILD';

상대:

select sum(t.hours)
from timesheets t
     join departents d on d.dept_id = t.dept_id
     join timesheet_statuses s on s.status_id = t.status_id
     join projects p on p.project_id = t.project_id
     join tasks k on k.task_id = t.task_id
where d.dept_code = 'HR'
and s.status = 'VALID'
and p.project_code = 'MYPROJECT'
and k.task_code = 'BUILD';

다음이 좋은 생각이라고 진지하게 생각하는 사람이 없는 한?

select sum(t.hours)
from timesheets t
where t.dept_id = 34394
and t.status_id = 89    
and t.project_id = 1253
and t.task_id = 77;

"하지만" 누군가는 "MY PROJECT, VALID 또는 HR의 코드가 바뀌면 어떻게 됩니까?"라고 말할 것입니다.제 대답은 "왜 바꿔야 합니까?" 입니다.일부 외부 기관이 앞으로 'VALID'를 'GOOD'로 다시 코딩하도록 입법할 것이라는 점에서 이러한 키는 "자연스러운" 키가 아닙니다. 실제로 이러한 범주에 해당하는 "자연스러운" 키는 SSN과 우편번호가 일반적인 예입니다.저는 분명히 사람, 주소와 같은 테이블에 의미 없는 숫자 키를 사용할 것입니다. 그러나 어떤 이유에서인지 대부분의 사람들이 옹호하는 것처럼 보이는 모든 것에 대해서는 아닙니다.

참고 항목: 다른 질문에 대한 내 답변

대리 키는 변경할 이유가 없습니다.나는 자연 키에 대해서 같은 말을 할 수 없습니다.성, 이메일, ISBN 번호 - 모두 언젠가 바뀔 수 있습니다.

대리 키(일반적으로 정수)는 테이블 관계를 더 빠르고 저장 및 업데이트 속도를 더 경제적으로 만들 수 있는 부가 가치를 제공합니다(비즈니스 키 필드와 달리 때때로 변경되는 대리 키를 사용할 때는 외국 키를 업데이트할 필요가 없습니다).

테이블의 기본 키는 주로 조인 목적으로 행을 고유하게 식별하는 데 사용되어야 합니다.사용자 테이블을 생각해 보십시오. 이름은 변경될 수 있으며 고유한 이름이 보장되지 않습니다.

생각하는 회사: 당신은 메르키아에 있는 다른 회사들과 사업을 하는 행복한 메르킨 회사입니다.당신은 회사 이름을 기본 키로 사용하지 않을 만큼 똑똑하기 때문에 영숫자 10자 전체에 메르키아 정부 고유의 회사 ID를 사용합니다.그러면 Merkia가 회사 ID를 바꾸는 것이 좋을 것 같아서요.괜찮아요, 당신은 처음부터 당신과 관련되지 않는 변경을 위해 당신의 db 엔진의 계단식 업데이트 기능을 사용합니다.나중에 비즈니스가 확장되고 이제 프리도니아에 있는 회사와 함께 일하게 됩니다.Freedonian 회사 ID는 최대 16자입니다.회사 ID 기본 키(Orders, Issues, Money Transfers 등의 외국 키 필드)를 확대하고 기본 키(외국 키)에 국가 필드를 추가해야 합니다.아야! 프리도니아의 내전은 세 나라로 나뉘어 있습니다.동료의 국가 이름을 새 국가 이름으로 변경해야 합니다. 구조가 단계적으로 업데이트됩니다.그건 그렇고, 당신의 주된 열쇠는 무엇입니까? (국가, 회사)ID) 또는 (회사)아이디, 국가)?후자는 가입에 도움이 되고, 전자는 다른 지수(또는 국가별로 주문을 그룹화하려면 많은 지수)를 피합니다.

이 모든 것이 증명은 아니지만, 가입 작업을 포함한 모든 용도의 행을 고유하게 식별하는 대리 키가 비즈니스 키보다 더 바람직하다는 것을 나타냅니다.

저는 대리 키를 전반적으로 싫어합니다.품질 좋은 자연 키가 없을 때만 사용해야 합니다.여러분이 그것을 생각할 때, 여러분의 테이블에 무의미한 데이터를 추가하는 것이 상황을 더 좋게 만들 수 있다고 생각하는 것은 다소 황당한 일입니다.

이유는 다음과 같습니다.

자연 키를 사용할 경우 테이블은 가장 자주 검색되는 방식으로 군집화되므로 쿼리를 더 빠르게 만듭니다.
대리 키를 사용하는 경우 논리 키 열에 고유한 인덱스를 추가해야 합니다.논리적 중복 데이터를 방지해야 합니다.예를 들어, pk가 대리 ID 열이더라도 조직 테이블에 같은 이름을 가진 두 조직을 허용할 수 없습니다.
대리 키를 기본 키로 사용하면 기본 키가 무엇인지 훨씬 명확하지 않습니다.개발할 때 테이블을 고유하게 만드는 열 집합을 알고자 합니다.
하나에서 많은 관계 체인, 논리적 키 체인.예를 들어, 조직에는 많은 계정이 있고 계정에는 많은 송장이 있습니다.따라서 조직의 논리적 키는 OrgName입니다.Accounts의 논리적 키는 OrgName, Account입니다.ID. 송장의 논리 키는 OrgName, Account입니다.ID, 송장 번호.

대리 키를 사용할 경우 키 체인은 직계 부모에 대한 외부 키만 가짐으로써 잘립니다.예를 들어, Invoice 테이블에는 OrgName 열이 없습니다.계정에 대한 열만 있습니다.ID. 특정 조직의 송장을 검색하려면 조직, 계정 및 송장 테이블에 가입해야 합니다.논리 키를 사용하는 경우 조직 테이블을 직접 조회할 수 있습니다.
룩업 테이블의 대리 키 값을 저장하면 테이블이 무의미한 정수로 채워집니다.데이터를 보려면 모든 룩업 테이블에 조인하는 복잡한 뷰를 만들어야 합니다.룩업 테이블은 열에 대해 허용 가능한 값 집합을 보유하는 것을 의미합니다.대신 정수 대리 키를 저장하여 암호화하면 안 됩니다.정규화 규칙에는 값 자체 대신 대리 정수를 저장해야 한다는 내용이 없습니다.
저는 3권의 데이터베이스 북을 가지고 있습니다.그들 중 하나도 대리 키를 사용하는 모습을 보여주지 않았습니다.

저는 이 끝없는 전쟁에 대한 저의 경험을 여러분과 나누고 싶습니다: 자연적인 것 대 대리적인 것의 딜레마에 대한 딜레마입니다.저는 대리 키(인공 자동 생성 키)와 자연 키(도메인 의미의 열로 구성) 모두 장단점이 있다고 생각합니다.따라서 상황에 따라 한 가지 방법을 선택하는 것이 더 적합할 수 있습니다.

많은 사람들이 대리키를 거의 완벽한 해결책으로, 자연키를 페스트처럼 제시하는 것처럼 보이기 때문에, 저는 다른 관점의 주장에 초점을 두겠습니다.

대리키의 단점

대리 키는 다음과 같습니다.

성능 문제의 원인:
- 이들은 일반적으로 다음을 의미하는 자동 증분 열을 사용하여 구현됩니다.
  - 새로운 ID를 얻고 싶을 때마다 데이터베이스로 왕복 이동(캐싱 또는 [seq]hilo 유사 알고리즘을 사용하여 이를 개선할 수 있다는 것을 알고 있지만 여전히 이러한 방법에는 고유한 단점이 있습니다.)
  - 어느 날 한 스키마에서 다른 스키마로 데이터를 이동해야 하는 경우(적어도 회사에서는 정기적으로 발생합니다) ID 충돌 문제가 발생할 수 있습니다.그리고 예 UUID를 사용할 수 있다는 것을 알고 있지만 UUID는 32자리의 16진수가 필요합니다. 데이터베이스 크기에 관심이 있다면 문제가 될 수 있습니다.
  - 모든 대리 키에 대해 하나의 시퀀스를 사용하는 경우 데이터베이스에서 경합이 발생합니다.
오류가 발생하기 쉽습니다.시퀀스에는 max_value 제한이 있으므로 개발자로서 다음 사항에 주의해야 합니다.
- 시퀀스를 순환해야 합니다(최대 값에 도달하면 1,2,...로 돌아갑니다).
- 시퀀스를 데이터의 순서 지정(시간 경과)으로 사용하는 경우 순환하는 경우(아이디 1의 열이 아이디 max-value - 1의 행보다 새 열일 수 있음)를 처리해야 합니다.
- 코드(그리고 내부 ID로 간주되어 발생하지 않아야 하는 클라이언트 인터페이스)가 시퀀스 값을 저장하는 데 사용한 32b/64b 정수를 지원하는지 확인합니다.
중복되지 않는 데이터는 보장하지 않습니다.모든 열 값은 같지만 생성된 값은 다른 두 행을 항상 가질 수 있습니다.저는 데이터베이스 설계 관점에서 대리 키의 문제입니다.
Wikipedia(위키백과)에서 자세히 보기...

자연열쇠에 대한 신화

복합 키는 대리 키보다 덜 비효율적입니다.아니요! 사용하는 데이터베이스 엔진에 따라 다릅니다.
- 오라클
- MySQL
자연 키는 실제 현실에서 존재하지 않습니다.죄송하지만 존재합니다!예를 들어, 항공 산업에서 다음 튜플은 주어진 스케줄된 비행(항공사, 출발 날짜, 비행 번호, 운항 서픽스)과 관련하여 항상 고유합니다.일반적으로 비즈니스 데이터 세트가 주어진 표준에 따라 고유하다고 보장될 때 이 데이터 세트는 [좋은] 자연스러운 키 후보가 됩니다.
자연 키는 하위 테이블의 스키마를 오염시킵니다.저에게는 이것이 진짜 문제라기보다는 느낌입니다.각각 2바이트의 기본 키가 4열인 것이 11바이트의 단일 열보다 더 효율적일 수 있습니다.또한 4개의 열을 사용하여 상위 테이블에 가입하지 않고 하위 테이블을 직접 쿼리할 수 있습니다(where 절의 4개 열을 사용).

결론

관련이 있을 때는 자연 키를 사용하고, 사용하는 것이 더 좋을 때는 대리 키를 사용합니다.

이것이 누군가에게 도움이 되었기를 바랍니다!

항상 비즈니스 의미가 없는 키를 사용합니다.좋은 연습일 뿐입니다.

편집: 온라인에서 링크를 찾으려고 했지만 찾을 수 없었습니다.그러나 '기업 건축의 패턴' [파울러]에서는 키가 되는 것 외에 아무런 의미가 없는 키 이외의 다른 것을 사용해서는 안 되는 이유에 대해 잘 설명하고 있습니다.한 가지 직업과 한 가지 직업만을 가져야 한다는 것으로 귀결됩니다.

ORM 도구를 사용하여 데이터 클래스를 처리/생성하려는 경우 대리 키가 매우 유용합니다.일부 고급 맵퍼(읽기: 최대 절전 모드)와 함께 복합 키를 사용할 수 있지만 코드에 약간의 복잡성이 추가됩니다.

(물론 데이터베이스 순수주의자들은 대리키라는 개념조차도 혐오스럽다고 주장할 것입니다.)

저는 적합할 때 대리 키에 UID를 사용하는 것을 좋아합니다.키를 미리 알고 있다는 것이 가장 큰 장점입니다. 예를 들어, ID가 이미 설정되어 있고 고유하다고 보장된 클래스의 인스턴스를 생성할 수 있지만 정수 키의 경우 0 또는 -1로 기본 설정되어 저장/업데이트할 때 적절한 값으로 업데이트해야 합니다.

UID는 조회 및 가입 속도 측면에서 벌칙이 있지만, 적합한지 여부는 해당 애플리케이션에 따라 달라집니다.

제 생각에는 대리키를 사용하는 것이 더 좋을 것 같아요. 바뀔 가능성이 전혀 없기 때문이에요.자연스러운 키로 사용할 수 있는 거의 모든 것이 바뀔 수 있습니다(거부자: 항상 사실은 아니지만 공통적으로).

자동차 DB가 그 예가 될 수 있습니다. 얼핏 보면 번호판이 열쇠로 사용될 수 있다고 생각할 수도 있습니다.하지만 이것들이 바뀔 수도 있으니 그건 좋지 않은 생각입니다.앱을 출시한 후에 누군가가 자신의 번호판을 반짝이는 새 개인 맞춤형 번호판으로 바꿀 수 없는 이유를 알고 싶어할 때 이 사실을 알고 싶지는 않을 것입니다.

가능한 경우 항상 단일 열, 대리 키를 사용합니다.이렇게 하면 기록을 유지하기 위해 단일 정보만 추적할 수 있기 때문에 조인은 물론 삽입/업데이트/삭제도 훨씬 깔끔해집니다.

그런 다음 필요에 따라 비즈니스 키를 고유한 제약 조건 또는 인덱스로 쌓습니다.이렇게 하면 데이터 무결성이 유지됩니다.

비즈니스 로직/자연 키는 변경될 수 있지만 테이블의 물리적 키는 절대 변경되지 않습니다.

데이터 웨어하우스 시나리오에서는 대리 키 경로를 따르는 것이 더 낫다고 생각합니다.두 가지 이유:

사용자는 소스 시스템과 독립적이며, 데이터 유형 변경과 같은 소스 시스템의 변경은 사용자에게 영향을 주지 않습니다.
대리 키에 정수 데이터 형식만 사용하므로 DW에 필요한 물리적 공간이 줄어듭니다.또한 당신의 인덱스는 더 잘 작동할 것입니다.

사례 1: 귀하의 테이블은 50개 미만의 레코드(50종류)가 있는 룩업 테이블입니다.

이 경우 각 레코드의 의미에 따라 수동으로 명명된 키를 사용합니다.

예:

Table: JOB with 50 records
CODE (primary key)       NAME               DESCRIPTION
PRG                      PROGRAMMER         A programmer is writing code
MNG                      MANAGER            A manager is doing whatever
CLN                      CLEANER            A cleaner cleans
...............
joined with
Table: PEOPLE with 100000 inserts

foreign key JOBCODE in table PEOPLE
looks at
primary key CODE in table JOB

사례 2: 당신의 테이블은 수천개의 기록이 있는 테이블입니다.

대리 키/자동 증가 키를 사용합니다.

예:

Table: ASSIGNMENT with 1000000 records
joined with
Table: PEOPLE with 100000 records

foreign key PEOPLEID in table ASSIGNMENT
looks at
primary key ID in table PEOPLE (autoincrement)

첫 번째 경우:

테이블의 모든 프로그래머를 선택할 수 있습니다.PEOPLE테이블과 결합하지 않고JOB, 하지만 단지 다음과 함께:SELECT * FROM PEOPLE WHERE JOBCODE = 'PRG'

두 번째 경우:

기본 키가 정수이므로 데이터베이스 쿼리가 더 빠릅니다.
데이터베이스 자체가 다음 자동 증분을 제공하므로 다음 고유 키를 찾는 데 애쓸 필요가 없습니다.

대리 키는 비즈니스 정보가 변경되거나 동일한 경우에 유용할 수 있습니다.결국, 사업명이 전국적으로 유일할 필요는 없습니다.스미스 일렉트로닉스라는 이름의 두 회사를 상대한다고 가정해 보겠습니다. 하나는 캔자스, 하나는 미시간입니다.주소별로 구분이 가능하지만, 변경됩니다.심지어 주는 변할 수도 있습니다. 만약 캔자스시티의 스미스 전자회사가 강을 건너 미주리주의 캔자스시티로 이사를 간다면 어떨까요?이러한 비즈니스를 자연스러운 키 정보와 구별할 수 있는 명확한 방법이 없으므로 대리 키는 매우 유용합니다.

ISBN 번호와 같은 대리 키를 생각해 보세요.보통 책을 제목과 저자별로 식별합니다.하지만 저는 H. P. 윌모트의 "진주만"이라는 제목의 책 두 권을 가지고 있는데, 그것들은 다른 판본일 뿐만 아니라 확실히 다른 책들입니다.그런 경우에는 책의 겉모습을 참고할 수도 있고, 앞의 것과 뒤의 것을 참고할 수도 있겠지만, 저는 ISBN에 의지할 수 있는 것이 좋습니다.

참고로, 클러스터된 인덱스를 임의의 대리 키(즉, XY8D7-DFD8S를 읽는 GUID)에 배치하는 것은 좋은 방법이 아닙니다. SQL Server는 이러한 데이터를 물리적으로 정렬할 수 없기 때문입니다.대신 이러한 데이터에 고유한 인덱스를 배치해야 하지만, 기본 테이블 작업에 대해 SQL 프로파일러를 실행한 후 데이터베이스 엔진 튜닝 관리자에 해당 데이터를 배치하는 것도 유용합니다.

스레드 @ http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be 참조

이것은 대리 키가 항상 말이 되는 경우 중 하나입니다.데이터베이스에 가장 적합한 것 또는 객체 모델에 가장 적합한 것을 선택하는 경우도 있지만, 두 경우 모두 의미 없는 키나 GUID를 사용하는 것이 더 좋습니다.인덱싱을 더 쉽고 빠르게 수행할 수 있으며 개체에 대한 ID도 변경되지 않습니다.

시점 데이터베이스의 경우 대리 키와 자연 키의 조합이 가장 좋습니다. 예를 들어 클럽의 회원 정보를 추적해야 합니다.구성원의 일부 속성은 변경되지 않습니다.예: 생년월일이지만 이름은 변경할 수 있습니다.따라서 member_id surgate key로 Member 테이블을 만들고 DOB에 대한 열을 갖습니다.사용자 이름이라는 다른 테이블을 만들고 member_id, member_fname, member_lname, date_update에 대한 열을 가집니다.이 표에서 자연 키는 member_id + date_update입니다.

코스용 말.제 편견을 말씀드리자면, 제가 먼저 개발자이기 때문에 주로 사용자들에게 작동하는 애플리케이션을 제공하는 것에 관심이 있습니다.

저는 자연스러운 키를 가진 시스템을 연구해 왔고, 가치 변화가 파급될 수 있도록 많은 시간을 할애해야 했습니다.

저는 대리 키만 있는 시스템을 연구해 보았는데, 유일한 단점은 분할을 위한 정규화되지 않은 데이터가 부족하다는 것입니다.

제가 함께 일했던 대부분의 기존 PL/SQL 개발자들은 조인당 테이블 수 때문에 대리 키를 좋아하지 않았지만, 테스트 및 프로덕션 데이터베이스는 결코 땀을 흘리지 않았습니다. 추가 조인은 애플리케이션 성능에 영향을 미치지 않았습니다."X.a = Y.b에 X 내부 조인 Y"와 같은 절을 지원하지 않는 데이터베이스 방언이나 해당 구문을 사용하지 않는 개발자의 경우 대리 키에 대한 추가 조인은 쿼리를 읽기 어렵고 입력 및 확인 시간이 길어집니다. @Tony Andrews post 참조.그러나 ORM이나 다른 SQL 생성 프레임워크를 사용하면 이를 알아채지 못할 것입니다.터치 타이핑도 완화됩니다.

아마도 이 주제와 완전히 관련이 없는 것 같지만, 대리 키를 다루느라 머리가 아픕니다.Oracle이 사전 제공한 분석 기능은 창고에 있는 모든 치수 테이블에 자동 생성된 SK를 생성하고, 이를 사실에 저장합니다.따라서 새로운 열이 추가될 때마다 또는 차원의 모든 항목에 대해 해당 열(차원)을 다시 로드해야 할 때마다 업데이트 중에 할당된 SK는 SK가 사실에 저장된 원래 값과 동기화되지 않게 되므로 SK에 연결된 모든 사실 테이블을 완전히 다시 로드해야 합니다.SK가 무의미한 숫자라고 해도 원본/오래된 기록으로 바뀔 수 없는 방법이 있었으면 좋겠습니다.많은 사람들이 알고 있듯이, 즉시 사용할 수 있는 서비스는 조직의 요구 사항을 거의 충족시키지 못하므로 끊임없이 맞춤화해야 합니다.현재 창고에 3년치 데이터가 보관되어 있으며 Oracle Financial 시스템에서 다시 로드되는 전체 용량은 매우 큽니다.따라서 제 경우에는 데이터 입력에서 생성된 것이 아니라 성능 보고를 돕기 위해 창고에 추가된 것입니다.알아요, 하지만 우리 것은 정말 달라졌어요, 그리고 악몽이에요.

언급URL : https://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys