このページは、以下の英語ページの抄訳です。
https://blogs.sap.com/2013/10/30/from-the-archives-surrogate-identifiers/
この記事のオリジナルは、Glenn Paulley が sybase.com に 2008 年 12 月に掲載したものです。その中で、Glenn は データベーススキーマにサロゲートキーを使用する背景と洞察について語っています。
識別子を自社でコントロールできるよう、自社のサロゲート識別子を定義することが良い方法であることは多いものです。
「自社でコントロール」の意味は、自社で識別子のフォーマット、価値範囲、その他プロパティを定義するということであり、（官公庁や政府関連事業者など）第三者ではないということです。
なぜでしょうか? 後ほどもう少し詳細を説明しますが、いくつかの理由があります。
重要な理由の1つとして、外部のソースが特定した場合の重複、変更、または識別子が単純に不正確である可能性をなくすことです。
この方法は、一般的にはよく理解されていると思います。
例えば、通常、ある人を識別する目的で一般の固定電話の番号を使用することはあまりありません。なぜならば、電話自体は他者とシェアすることができ、電話番号も変更できるからです。
そのため、もしサロゲート識別子が良い方法である場合、次の質問は「その作成方法は？」になるでしょう。
私はこの質問を頻繁に目にしてきました。
可能性の 1 つとして、
[GLOBAL] AUTOINCREMENT (または
IDENTITY フィールド)、GUID、sequences (Oracleを使っている場合)、like などのビルトインのサロゲートキー生成メカニズムを使用することです。
SQL Anywhere では、autoincrement カラムは、シンプルに integer 値の数値型で格納されます。また
GLOBAL AUTOINCREMENT で、分散されたデータベースにわたって生成された値はユニークであると保証されます。
なぜならば、値は 2 等分 にパーティション化され、上位の値は「データベース」の値で、下位の値は特定のサイトのテーブルのインクリメント値だからです。
Autoincrement 値も GUID 生成もどちらもユニークな値をイールドします。
また、どちらのメカニズムも Hibernate などいくつかの ORM パッケージでサポートされています。
Mobile Link を使用した場合には、
GLOBAL AUTOINCREMENT または GUID 識別子を推奨しています。なぜならば、これらであれば、分散したキーの競合を避けることができるからです。
簡単に言うと、autoincrement と GUID 値が解決する 2 つのテクニカルな問題は、
(1) キーの競合の可能性を取り除く、
(2) アプリケーション内にメカニズムを作るよりも、キー値を生成するためのサーバーでビルトインの仕組みを使用することによって効率的にそれができる、
ということです。
過去の Sybase Techwave で autoincrement と GUID 値がこれら２つのテクニカルな問題を解決するという同様のプレゼンを行った時に話に来られた方には申し訳ありませんが、他にも考慮すべき問題がありました。
それらを以下に挙げます。
- 私の経験では、サロゲート識別子を「隠す」のは非常に難しいことです。
おそらくアプリケーション開発者が必死に行ったとしても、識別子はいずれ企業内ユーザーまたは外部の人々や組織（お客様など）、あるいはどちらにも露わになってしまうでしょう。
システムと業務プロセスのどちらのデバックも容易なことから、重要なビジネスオブジェクトではサロゲート識別子を自社のフォーマットによって区別するのも良い方法です。
例えば、ある企業はお客様識別子 XYZ456、製品コード 7877-876、そしてサプライヤー識別子 879BC のようなものを持っているとします。これらを（それらの値に情報を埋め込まず）フォーマットによって区別することによって、オブジェクトのクラスを瞬時に認識できます。
これによって、お客様のような第三者とインタラクトする場合の時間と混乱を減らすことができます。
Bell Canada が電話番号と同じフォーマットの 7 桁または 10 桁の顧客番号を使用した場合の混乱を想像してみてください。アルファベットの値を使用した場合には、キー値の一部として単語を形成しないように（望ましくない可能性が高いので）母音を使用しないように注意する必要があります。
- Autoincrement と GUID 識別子は、セルフチェッキングなものではありません。
カナダでは (US とは異なり）社会保障 (ソーシャルセキュリティー) 番号 (SIN) はセルフチェッキングです。
これは 8 桁＋最後の 1 桁はチェックディジットです。
チェックデジットは、2 桁の SIN をトランスポジションし、同じチェックディジットをイールドする可能性を除外するアルゴリズムで形成されています（カナダ歳入庁が公開したもの）。
単純には見つけられない無効な識別子と有効な識別子を区別することができるようにすることで、サロゲート識別子に対しても同様のテクニックを使用することが可能です。
これは、コールセンターなどのような環境において現実的なメリットになります。
- 特に、数値が 3 または 4 桁よりも長い場合には、autoincrement (integer) 値での transposition エラーは現実的な懸念点です。GUID も、同様の問題で苦労します。
GUID 値がバーチャルにどのバイナリー文字列にもなりうるという理由だけでさらにたいへんです。
GUID 値の長さは、タイピングを難しくし、トランスポジションや他のエラーの可能性をもたらします。
また、GUID 値はその長さのために index 内のキー値としては非効率です。
- 識別子をタイプしづらくすることでエラーの頻度を減らすことができます。
これは、何十年も前にカナダの郵便番号を導入した時の背景となる推進力でした。
カナダの郵便番号は、3文字のグループが 2 つの計 6 文字で構成されています。
例えば、iAnywhere の本社のある Waterloo の郵便番号は、N2L 6R2です。
交互の文字-数字のパターンは、ブラインドタッチの熟練タイピストを除いて、難しいものでした。
データ入力速度を落とすことで、エラーは削減されました。
さらに、固定のパターンにより、「5」を「S」と入力したり、「L」を「1」と入力する間違いも不可能になりました。
私はデータベース管理者としてこの方法を 20 年前に紹介されました。これらは、未だに良い方法ではあります。
しかしながら、その会社のサロゲートキーを生成するには、確実にスケールするように気をつけてアプリケーションソフトウェアのコードを書く必要があります。
私は現在取り組んでいるあるお客様の問題がきっかけとなり、この記事を書くことにしました。
このお客様は、個々を識別するために autoincrement 値を使用しています。
識別子値でオンライン lookup を行う場合、アプリケーションは、
SELECT TOP クエリーと
FASTFIRSTROW ヒントを生成し、ユーザーがタイプした桁で始まる識別子を持つ全ての個人のリストを表示します。
しかしながら autoincrement を使用しているため、ユーザーが「1」をタイプした場合には、10 から19 の間、100 から 199 の間、1000 から 1999 の間、..など、識別子が「1」である有効な個人が存在する可能性があります。
これは、最初の桁に基づき、（マッチするローが多数あるため）最初のクエリーをどちらも非効率なものにしてしまい、結局価値のないものになりがちです。
そのかわりに、システムがその識別子に特定のパターン（完全に数値のままだとしても）を指定する場合、
例えば 7 桁（例9999999) など、
識別子値による検索では、サーバーに送られるクエリー数を削減し、より精度を上げた検索結果が得られる価値ある検索にするために、ユーザーが十分な桁数をタイプするまで（例えば 4 または 5）はデファーされる可能性があります。
なぜならば、
BETWEEN 述部が使用されている可能性があるからです。
===
SQL Anywhere に関してはまずは
こちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。
その他 や、
SAP SCN Wiki などもご参照ください。
SQL Anywhere に関して技術的な質問のある方は
コミュニティに登録し、
「Ask a Question」機能をご利用ください。
Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」
を入力してください。
不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。
======================
ご購入に関するお問い合わせ
こちらよりお問い合わせください。
