복합 기본 키와 고유 한 개체 ID 필드

복합 기본 키와 고유 한 개체 ID 필드

---

저는 복합 키가 고유 한 개체 ID 필드를 사용하는 것보다 훨씬 이상적이며 데이터베이스를 구축 할 때 단일 고유 ID를 기본 키로 사용 해서는 안된다는 생각으로 구축 된 데이터베이스를 상속했습니다 . 이 데이터베이스를 위해 Rails 프런트 엔드를 구축하고 있었기 때문에 Rails 규칙을 준수하는 데 어려움을 겪었습니다 (사용자 정의 뷰와 몇 가지 추가 gem을 사용하여 복합 키를 처리하는 것이 가능했지만).

이 특정 스키마 디자인 뒤에있는 이유는 데이터베이스가 비효율적 인 방식으로 ID 필드를 처리하는 방법과 인덱스를 작성할 때 트리 정렬에 결함이있는 것과 관련이 있습니다. 이 설명에는 깊이가 없었고 여전히 개념에 대해 머리를 감싸려고 노력하고 있습니다 (복합 키 사용에 익숙하지만 시간의 100 %는 아님).

누구든지이 주제에 대한 의견을 제시하거나 더 깊이 추가 할 수 있습니까?

---

일반적으로 사용되는 대부분의 엔진 (MS SQL Server, Oracle, DB2, MySQL 등)은 대리 키 시스템을 사용하여 눈에 띄는 문제가 발생하지 않습니다. 일부는 대리자를 사용하여 성능이 향상 될 수도 있지만 성능 문제는 플랫폼에 따라 다릅니다.

일반적으로 자연적 키 (그리고 더 나아가 복합 키) 대 대리 키 논쟁은“정답”이 보이지 않는 오랜 역사를 가지고 있습니다.

자연 키 (단수 또는 복합)에 대한 인수에는 일반적으로 다음이 포함됩니다.

1) 이미 데이터 모델에서 사용할 수 있습니다. 모델링되는 대부분의 엔터티에는 관계 생성을위한 키의 요구 사항을 충족하는 하나 이상의 속성 또는 속성 조합이 이미 포함되어 있습니다. 각 테이블에 추가 속성을 추가하면 불필요한 중복이 통합됩니다.

2) 특정 조인이 필요하지 않습니다. 예를 들어 고객 코드가있는 고객과 송장 번호가있는 송장 (둘 다 "자연"키임)이 있고 특정 고객 코드에 대한 모든 송장 번호를 검색하려는 경우 간단히 "SELECT InvoiceNumber FROM Invoice WHERE CustomerCode = 'XYZ123'". 고전적인 대리 키 접근 방식에서 SQL은 다음과 같습니다 "SELECT Invoice.InvoiceNumber FROM Invoice INNER JOIN Customer ON Invoice.CustomerID = Customer.CustomerID WHERE Customer.CustomerCode = 'XYZ123'"..

3) 데이터 모델링에보다 보편적으로 적용 가능한 접근 방식에 기여합니다. 자연 키를 사용하면 동일한 디자인을 다른 SQL 엔진간에 크게 변경하지 않고 사용할 수 있습니다. 많은 대리 키 접근 방식은 키 생성을 위해 특정 SQL 엔진 기술을 사용하므로 다양한 플랫폼에서 구현하기 위해 데이터 모델의 전문화가 필요합니다.

서로 게이트 키에 대한 인수는 SQL 엔진과 관련된 문제를 중심으로 진행되는 경향이 있습니다.

1) 비즈니스 요구 사항 / 규칙이 변경 될 때 속성을 더 쉽게 변경할 수 있습니다. 이는 데이터 속성을 단일 테이블로 분리 할 수 ​​있기 때문입니다. 이것은 주로 DOMAIN과 같은 표준 SQL 구문을 효율적으로 구현하지 않는 SQL 엔진의 문제입니다. DOMAIN 문에 의해 속성이 정의되면 ALTER DOMAIN 문을 사용하여 스키마 전체에서 속성 변경을 수행 할 수 있습니다. 다른 SQL 엔진은 도메인 변경에 대해 다른 성능 특성을 가지며 일부 SQL 엔진은 DOMAINS를 전혀 구현하지 않으므로 데이터 모델러는 대리 키를 추가하여 속성을 변경하는 기능을 개선하여 이러한 상황을 보완합니다.

2) 자연 키보다 동시성을 쉽게 구현할 수 있습니다.자연 키의 경우 두 사용자가 고객 행과 같은 동일한 정보 세트로 동시에 작업하고 사용자 중 한 명이 자연 키 값을 수정하면 두 번째 사용자의 업데이트가 실패합니다. 업데이트는 더 이상 데이터베이스에 존재하지 않습니다. 대리 키의 경우 변경 가능한 고객 코드가 아닌 변경 불가능한 ID 값이 데이터베이스의 행을 식별하는 데 사용되기 때문에 업데이트가 성공적으로 처리됩니다. 그러나 두 번째 업데이트를 허용하는 것이 항상 바람직한 것은 아닙니다. 고객 코드가 변경된 경우 행의 실제 "ID"가 변경 되었기 때문에 두 번째 사용자가 변경을 진행하지 못하도록 할 수 있습니다. 두 번째 사용자는 잘못된 행을 업데이트하고 있습니다. 대리 키나 자연 키 자체로는이 문제를 해결할 수 없습니다.

3) 자연스러운 키보다 성능이 좋습니다. 성능은 SQL 엔진의 가장 직접적인 영향을받습니다. 다른 SQL 엔진을 사용하여 동일한 하드웨어에 구현 된 동일한 데이터베이스 스키마는 SQL 엔진의 데이터 저장 및 검색 메커니즘으로 인해 성능 특성이 크게 달라지는 경우가 많습니다. 일부 SQL 엔진은 고객 코드와 같은 동일한 속성이 데이터베이스 스키마의 여러 ​​위치에 나타날 때 데이터가 실제로 중복 저장되는 플랫 파일 시스템과 거의 비슷합니다. SQL 엔진에 의한 이러한 중복 스토리지는 데이터 또는 스키마를 변경해야 할 때 성능 문제를 일으킬 수 있습니다. 다른 SQL 엔진은 데이터 모델과 저장 / 검색 시스템을 더 잘 분리하여 데이터와 스키마를 더 빠르게 변경할 수 있도록합니다.

4) 대리 키는 특정 데이터 액세스 라이브러리 및 GUI 프레임 워크에서 더 잘 작동합니다.대부분의 대리 키 디자인 (예 : 모든 관계형 키는 정수)의 동종 특성으로 인해 데이터 액세스 라이브러리, ORM 및 GUI 프레임 워크는 데이터에 대한 특별한 지식 없이도 정보와 함께 작동 할 수 있습니다. 이기종 특성 (다양한 데이터 유형, 크기 등)으로 인해 자연 키는 자동화 또는 반자동 툴킷 및 라이브러리와 잘 작동하지 않습니다. Embedded SQL 데이터베이스와 같은 특수 시나리오의 경우 특정 툴킷을 염두에두고 데이터베이스를 설계하는 것이 허용 될 수 있습니다. 다른 시나리오에서 데이터베이스는 여러 플랫폼, 응용 프로그램, 보고서 시스템 및 장치에서 동시에 액세스하는 엔터프라이즈 정보 리소스이므로 특정 라이브러리 또는 프레임 워크에 중점을두고 설계된 경우 제대로 작동하지 않습니다. 게다가,

나는 (분명히) 자연스러운 키 편에 빠지는 경향이 있지만 그것에 대해 열광적이지는 않습니다. 내가 작업하는 환경으로 인해 설계를 돕는 데이터베이스가 다양한 응용 프로그램에서 사용될 수 있기 때문에 대부분의 데이터 모델링에 자연 키를 사용하고 대리자를 거의 도입하지 않습니다. 그러나 대리자를 사용하는 기존 데이터베이스를 다시 구현하려고 시도하지 않습니다. 대리 키 시스템은 잘 작동합니다. 이미 잘 작동하는 것을 변경할 필요가 없습니다.

각 접근 방식의 장점을 설명하는 몇 가지 훌륭한 리소스가 있습니다.

http://www.google.com/search?q=natural+key+surrogate+key

http://www.agiledata.org/essays/keys.html

http://www.informationweek.com/news/software/bi/201806814

---

저는 15 년 동안 데이터베이스 응용 프로그램을 개발해 왔지만 아직 대리 키가 아닌 키가 대리 키보다 더 나은 선택 인 경우를 발견하지 못했습니다.

나는 그러한 경우가 존재하지 않는다고 말하는 것이 아니라 데이터베이스에 액세스하는 응용 프로그램을 실제로 개발하는 실제 문제를 고려할 때 일반적으로 대리 키의 이점이 비의 이론적 순도를 압도하기 시작한다고 말하는 것입니다. -대리 키

---

기본 키는 일정하고 의미가 없어야합니다 . 비 대리 키는 일반적으로 하나 또는 두 가지 요구 사항 모두에 실패하여 결국

• 키가 일정하지 않으면 매우 복잡해질 수있는 향후 업데이트 문제가 있습니다.

• 키가 의미가 없으면 변경 될 가능성이 더 높습니다. 즉, 일정하지 않습니다. 위 참조

간단하고 일반적인 예를 들어 보겠습니다. 인벤토리 항목 테이블입니다. 항목 번호 (sku 번호, 바코드, 부품 코드 등)를 기본 키로 만들고 싶은 유혹이있을 수 있지만 1 년 후 모든 항목 번호가 변경 되고 전체 업데이트가 매우 복잡해집니다. 데이터베이스 문제 ...

편집 : 철학적 것보다 더 실용적인 추가 문제가 있습니다. 많은 경우에 특정 행을 찾은 다음 나중에 업데이트하거나 다시 찾게됩니다 (또는 둘 다). 복합 키를 사용하면 추적 할 데이터가 더 많고 다시 찾기 또는 업데이트 (또는 삭제)를위한 WHERE 절에 더 많은 제약이 있습니다. 그 동안 주요 세그먼트 중 하나가 변경되었을 수도 있습니다!. 대리 키를 사용하면 항상 하나의 값 (대리 ID) 만 유지할 수 있으며 정의에 따라 변경할 수 없으므로 상황이 크게 단순화됩니다.

---

그것은 소리 데이터베이스가 서로 게이트 키 토론 대 위대한 자연 키의 자연 키 측에 만든 사람처럼.

ID 필드의 btree에 대한 문제는 들어 본 적이 없지만 심도있게 연구하지 않았습니다.

저는 서로 게이트 키쪽에 속합니다. 다른 테이블에서 단일 값만 반복하기 때문에 서로 게이트 키를 사용할 때 반복이 적습니다. 인간은 손으로 테이블을 조인하는 경우가 거의 없기 때문에 숫자인지 아닌지는 상관하지 않습니다. 또한 인덱스에서 조회 할 고정 크기 열이 하나뿐이므로 서로 게이트가 기본 키에 의한 조회 시간도 더 빠르다고 가정하는 것이 안전합니다.

---

'고유 (객체) ID'필드를 사용하면 조인이 간단 해지지 만 다른 (복합) 키는 여전히 고유해야합니다. null이 아닌 제약 조건을 완화하지 말고 고유 제약 조건을 유지하십시오.

DBMS가 고유 한 정수를 효과적으로 처리 할 수 ​​없다면 큰 문제가 있습니다. 그러나 '고유 (객체) ID'와 다른 키를 모두 사용하면 다른 키보다 더 많은 공간 (인덱스 용)을 사용하고 각 삽입 작업에서 업데이트 할 두 개의 인덱스가 있습니다. 따라서 공짜는 아니지만 원래 키도 유지하는 한 괜찮습니다. 다른 키를 제거하면 시스템 디자인이 손상됩니다. 결국 모든 지옥이 풀릴 것입니다 (그리고 당신은 지옥이 풀린 것을 발견 할 수도 있고 아닐 수도 있습니다).

---

저는 기본적으로 대리 키 팀의 일원이며 여기에 JeremyDWill이 제시 한 것과 같은 주장을 이해하고 이해하더라도 "자연"키가 대리 키보다 더 나은 경우를 찾고 있습니다.

이 문제를 다루는 다른 게시물은 일반적으로 관계형 데이터베이스 이론 및 데이터베이스 성능을 참조합니다. 이 경우 항상 잊혀진 또 다른 흥미로운 주장은 테이블 정규화 및 코드 생산성 과 관련이 있습니다 .

테이블을 만들 때마다 시간을 잃을 까

1. 기본 키 및 물리적 특성 (유형, 크기) 식별
2. 내 코드에서 참조 할 때마다 이러한 특성을 기억하고 있습니까?
3. 팀의 다른 개발자에게 내 PK 선택을 설명하는 것입니까?

내 대답은 다음 질문에 모두 아니오입니다.

1. 나는 사람 목록을 다룰 때 "가장 좋은 기본 키"를 식별하는 데 시간을 허비 할 시간이 없습니다.
2. " computer"테이블 의 기본 키가 64 자 길이의 문자열 이라는 사실을 기억하고 싶지 않습니다 (Windows에서 컴퓨터 이름에 대해 많은 문자를 허용합니까?).
3. 내 선택을 다른 개발자에게 설명하고 싶지 않습니다. 개발자 중 한 명이 "예,하지만 다른 도메인에서 컴퓨터를 관리해야한다고 생각하십니까?이 64 자 문자열을 사용하면 도메인 이름 + 컴퓨터 이름? ".

그래서 저는 지난 5 년 동안 매우 기본적인 규칙으로 작업 해 왔습니다. 각 테이블 ( ' myTable' 이라고 부르겠습니다 )에는 id_MyTableuniqueIdentifier 유형 인 ' ' 라는 첫 번째 필드 가 있습니다. 이 테이블이 ' ComputerUser'테이블 과 같은 "다 대다"관계를 지원하더라도 ' id_Computer'및 ' id_User' 의 조합이 매우 수용 가능한 기본 키를 형성하는 경우,이 ' id_ComputerUser'필드를 uniqueIdentifier 로 만드는 것을 선호합니다 . 규칙을 고수하기 위해.

가장 큰 장점은 코드 내에서 기본 키 및 / 또는 외래 키 사용에 대해 더 이상 신경 쓸 필요가 없다는 것입니다. 테이블 이름이 있으면 PK 이름과 유형을 알 수 있습니다. 데이터 모델에 구현 된 링크를 알고 나면 테이블에서 사용 가능한 외래 키의 이름을 알게됩니다.

I am not sure that my rule is the best one. But it is a very efficient one!

---

A practical approach to developing a new architecture is one that utilizes surrogate keys for tables that will contain thousands of multi-column highly unique records and composite keys for short descriptionary tables. I usually find that the colleges dictate the use of surrogate keys while the real world programmers prefer composite keys. You really need to apply the right type of primary key to the table - not just one way or the other.

---

using natural keys makes a nightmare using any automatic ORM as persistence layer. Also, foreign keys on multiple column tend to overlap one another and this will give further problem when navigating and updating the relationship in a OO way.

Still you could transform the natural key in an unique constrain and add an auto generated id; this doesn't remove the problem with the foreign keys, though, those will have to be changed by hand; hopefully multiple columns and overlapping constraints will be a minority of all the relationship, so you could concentrate on refactoring where it matter most.

natural pk have their motivation and usages scenario and are not a bad thing(tm), they just tend to not get along well with ORM.

my feeling is that as any other concepts, natural keys and table normalization should be used when sensible and not as blind design constraints

---

I'm going to be short and sweet here: Composite primary keys are not good these days. Add in surrogate arbitrary keys if you can and maintain the current key schemes via unique constraints. ORM is happy, you're happy, original programmer not-so-happy but unless he's your boss then he can just deal with it.

---

Composite keys can be good - they may affect performance - but they are not the only answer, in much the same way that a unique (surrogate) key isn't the only answer.

What concerns me is the vagueness in the reasoning for choosing composite keys. More often than not vagueness about anything technical indicates a lack of understanding - maybe following someone else's guidelines, in a book or article....

There is nothing wrong with a single unique ID - infact if you've got an application connected to a database server and you can choose which database you're using it will all be good, and you can pretty much do anything with your keys and not really suffer too badly.

There has been, and will be, a lot written about this, because there is no single answer. There are methods and approaches that need to be applied carefully in a skilled manner.

I've had lots of problems with ID's being provided automatically by the database - and I avoid them wherever possible, but still use them occasionally.

---

... how the database handles ID fields in a non-efficient manner and when it's building indexes, tree sorts are flawed ...

This was almost certainly nonsense, but may have related to the issue of index block contention when assigning incrementing numbers to a PK at a high rate from different sessions. If so then the REVERSE KEY index is there to help, albeit at the expense of a larger index size due to a change in block-split algorithm. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref998

Go synthetic, particularly if it aids more rapid development with your toolset.

---

I am not a experienced one but still i m in favor of Using primary key as id here is the explanation using an example..

The format of external data may change over time. For example, you might think that the ISBN of a book would make a good primary key in a table of books. After all, ISBNs are unique. But as this particular book is being written, the publishing industry in the United States is gearing up for a major change as additional digits are added to all ISBNs. If we’d used the ISBN as the primary key in a table of books, we’d have to update each row to reflect this change. But then we’d have another problem. There’ll be other tables in the database that reference rows in the books table via the primary key. We can’t change the key in the books table unless we first go through and update all of these references. And that will involve dropping foreign key constraints, updating tables, updating the books table, and finally reestablishing the constraints. All in all, this is something of a pain. The problems go away if we use our own internal value as a primary key. No third party can come along and arbitrarily tell us to change our schema—we control our own keyspace. And if something such as the ISBN does need to change, it can change without affecting any of the existing relationships in the database. In effect, we’ve decoupled the knitting together of rows from the external representation of data in those rows.

Although the explanation is quite bookish but i think it explains the things in a simpler way.

---

@JeremyDWill

Thank you for providing some much-needed balance to the debate. In particular, thanks for the info on DOMAINs.

I actually use surrogate keys system-wide for the sake of consistency, but there are tradeoffs involved. The most common cause for me to curse using surrogate keys is when I have a lookup table with a short list of canonical values—I'd use less space and all my queries would be shorter/easier/faster if I had just made the values the PK instead of having to join to the table.

---

You can do both - since any big company database is likely to be used by several applications, including human DBAs running one-off queries and data imports, designing it purely for the benefit of ORM systems is not always practical or desirable.

What I tend to do these days is to add a "RowID" property to each table - this field is a GUID, and so unique to each row. This is NOT the primary key - that is a natural key (if possible). However, any ORM layers working on top of this database can use the RowID to identify their derived objects.

Thus you might have:

CREATE TABLE dbo.Invoice (
  CustomerId varchar(10),
  CustomerOrderNo varchar(10),
  InvoiceAmount money not null,
  Comments nvarchar(4000),
  RowId uniqueidentifier not null default(newid()),

  primary key(CustomerId, CustomerOrderNo)
)

So your DBA is happy, your ORM architect is happy, and your database integrity is preserved!

---

I just wanted to add something here that I don't ever see covered when discussing auto-generated integer identity fields with relational databases (because I see them a lot), and that is, it's base type can an will overflow at some point.

Now I'm not trying to say this automatically makes composite ids the way to go, but it's just a matter of fact that even though more data could be logically added to a table (which is still unique), the single auto-generated integer identity could prevent this from happening.

Yes I realize that for most situations it's unlikely, and using a 64bit integer gives you lots of headroom, and realistically the database probably should have been designed differently if an overflow like this ever occurred.

But that doesn't prevent someone from doing it... a table using a single auto-generated 32bit integer as it's identity, which is expected to store all transactions at a global level for a particular fast-food company, is going fail as soon as it tries to insert it's 2,147,483,648th transaction (and that is a completely feasible scenario).

It's just something to note, that people tend to gloss over or just ignore entirely. If any table is going to be inserted into with regularity, considerations should be made to just how often and how much data will accumulate over time, and whether or not an integer based identifier should even be used.

참고URL : https://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field