Dev Study
← データの入口
レッスン 7 / 7·約 9 分·第17期 データの入口

一つの事実を一か所に置く — 正規化は、守らせたい事実をキーに変えること

リクエストがアプリケーションに届くまで
  1. ブラウザURL入力
  2. DNS名前→IP
  3. ルータ経路選択
  4. ロードバランサ振り分け
  5. Nginx受け口
  6. アプリ処理
  7. DB保存今ここ

前回、一意性は宣言したときにだけ存在することを見ました。最終回の今回は、宣言できる形になっていない事実を、宣言できる形に組み替える方法を見ます。

まず結論

顧客名を注文の行ごとに書き写した表で1行だけ名前を直すと、同じ顧客に名前が2つ残ります。データベースは壊れていません。「顧客1人に名前は1つ」とは、どこにも宣言されていなかったからです。

表を分けて顧客番号を顧客表の主キーにすると、その事実は「顧客番号ごとに行は1つ」という制約に置き換わり、データベースが守れる形になります。正規化とは、守らせたい事実をキーに変える作業です。

? 読む前に予測

注文表の3行すべてに同じ顧客の名前を書き写してあります。改姓の連絡を受けて、そのうち1行だけを UPDATE しました。PostgreSQL はどう振る舞うでしょうか。

当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。

抽象原理

事実には、置き場所の数がある

前回、一意性は宣言したときにだけ存在することを見ました。では、宣言できる形になっていない事実はどうするのか。

例として「顧客1人に名前は1つ」という事実を考えます。Codd は1971年の論文で、この種の関係を関数従属と呼び、こう定義しました。

Attribute B of relation R is functionally dependent on attribute A of R if, at every instant of time, each value in A has no more than one value in B associated with it under R.

「どの時点でも、Aの各値に対応するBの値は1つ以下」。顧客番号をA、名前をBとすれば、守らせたい事実そのものです。

ところが、注文の表に顧客名を毎行書き写すと、顧客7の名前は注文の数だけ別々の行に置かれます。この表の主キーは注文番号です。顧客番号には一意性を宣言できません。顧客7の注文は3行あるからです。事実はあるのに、それを制約として書く場所がありません。 書かれていない事実を、データベースは守りません。

同じ事実を、置き場所の数で比べる
守らせたい事実顧客番号から名前が1つに決まる
注文の行ごとに書き写す
置き場所は注文の数だけ宣言できる制約が無い
顧客番号を主キーにした表へ移す
置き場所は1つ主キー制約が行の数を1つに保つ
  • ・データベースが守るのは、宣言された主キーと制約だけ
  • ・正規化は、事実を宣言できる形(キー)に組み替える
具体実物

名前を1行だけ直す

PostgreSQL 18.6 で実際に試しました。まず顧客名を注文ごとに書き写した表です(実行した SQL のうち、冒頭の DROP TABLE とその通知は省略しています)。

psql -e — 顧客名を複製した表で、1行だけ直す(PostgreSQL 18.6)コマンド出力
CREATE TABLE orders_flat (order_id integer PRIMARY KEY, customer_id integer, customer_name text, item text);
CREATE TABLE
INSERT INTO orders_flat VALUES
(1, 7, 'Sato Hanako', 'pen'),
(2, 7, 'Sato Hanako', 'notebook'),
(3, 7, 'Sato Hanako', 'ink');
INSERT 0 3
UPDATE orders_flat SET customer_name = 'Suzuki Hanako' WHERE order_id = 3;
UPDATE 1
SELECT DISTINCT customer_id, customer_name FROM orders_flat WHERE customer_id = 7;
customer_id | customer_name 
-------------+---------------
         7 | Sato Hanako
         7 | Suzuki Hanako
(2 rows)

改姓の連絡を受けて、直近の注文3だけを直しました。UPDATE 1 は成功です。重複を除いて数えると、顧客7に名前が2つ出ます。データベースは言われた1行を正しく直し、言われなかった2行を残しました。

次に、名前を顧客表の1か所に置き、注文表は顧客番号だけを持つようにします。

psql -e — 顧客表に分け、JOIN で読む(PostgreSQL 18.6)コマンド出力
CREATE TABLE customers (customer_id integer PRIMARY KEY, name text);
CREATE TABLE
CREATE TABLE orders (order_id integer PRIMARY KEY, customer_id integer REFERENCES customers, item text);
CREATE TABLE
INSERT INTO customers VALUES (7, 'Sato Hanako');
INSERT 0 1
INSERT INTO orders VALUES (1, 7, 'pen'), (2, 7, 'notebook'), (3, 7, 'ink');
INSERT 0 3
UPDATE customers SET name = 'Suzuki Hanako' WHERE customer_id = 7;
UPDATE 1
SELECT o.order_id, o.customer_id, c.name, o.item FROM orders o JOIN customers c ON c.customer_id = o.customer_id ORDER BY o.order_id;
order_id | customer_id |     name      |   item   
----------+-------------+---------------+----------
      1 |           7 | Suzuki Hanako | pen
      2 |           7 | Suzuki Hanako | notebook
      3 |           7 | Suzuki Hanako | ink
(3 rows)
 
SELECT DISTINCT o.customer_id, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id;
customer_id |     name      
-------------+---------------
         7 | Suzuki Hanako
(1 row)

JOIN は、2つの表の行を指定した列の値が一致するものどうしで組み合わせて読む操作です。注文表は名前を持たないので、読むたびに顧客表の1行を見ます。直す場所も1つで、名前は1つだけ出ます。同じ手順を2回実行し、2回とも同じ出力でした。

✓ ここまでの確認

複製した表で `SELECT DISTINCT customer_id, customer_name ... WHERE customer_id = 7` が2行を返しました。これは何を意味しますか。

抽象原理

正規化は、事実をキーに変える

2つ目の表で何が変わったのかを、宣言の側から言い直します。PostgreSQL の公式文書は主キーをこう説明します。

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

customers の主キーは customer_id なので、顧客7の行は1行しか存在できません。名前の置き場所も1つです。「顧客番号から名前が1つに決まる」という事実が、「顧客番号ごとに行は1つ」という宣言できる制約に置き換わりました。 前回見た一意性の制約が、そのまま事実の番人になります。これがこの回の中心です。

Kent は1983年の解説で、第2・第3正規形を次のように要約しています。

a record is in second and third normal forms if every field is either part of the key or provides a (single-valued) fact about exactly the whole key and nothing else.

最初の表に当てはめると、customer_name はキーである注文番号についての事実ではなく、顧客番号についての事実です。だから顧客番号をキーとする表へ移しました。

第1正規形は、資料によって言い方が違います。

第1正規形まわりの記述(Codd 1970 §1.4 のみ要約、他は逐語)表
資料                  記述
Codd 1970 §1.3        simple domains-domains whose elements are atomic (nondecomposable) values
Codd 1970 §1.4        nonsimple domains を除く手続きを normalization と呼ぶ
Codd 1971 付録        none of its domains has elements which are themselves sets
Kent 1983             all occurrences of a record type must contain the same number of fields

atomic の語を使うのは Codd 1970 で、要素が atomic な値のドメインを単純なドメインと呼び、単純でないドメインを除く手続きを normalization と呼びました。「第1正規形」という名前と明示の定義は Codd 1971 の付録にあり、確認した本文(OCR テキスト)では、そこに atomic の語はありません。

もう1つ、Kent は update anomalies の一文と同じ段落でこう書きます。

There is no obligation to fully normalize all records when actual performance requirements are taken into account.

正規化した形は読み出しに不利になりうると Kent 自身が述べており、性能のために写しを持つ判断はこの範囲で成り立ちます。そのとき写しの整合は、データベースではなく、写しを持つと決めた側が引き受けます。

具体実物

DNS の別名は、写しではなく指し示す

別の領域にも同じ組み方があります。DNS では、1つのサーバに複数の名前を付けることがあります。たとえば www.example.com を example.com の別名にする場合です。別名の側にアドレスを書き写すこともできそうですが、DNS には CNAME という仕組みがあります。「この名前は、あの正規の名前の別名だ」とだけ書き、アドレスは正規の名前の側に1か所だけ置くものです。

RFC 1034 §3.6.2 は、CNAME を置いた名前にほかのデータを置かない決まりの理由をこう書きます(規範語の大文字表記が定められる前の文書なので、should は小文字です)。

If a CNAME RR is present at a node, no other data should be present; this ensures that the data for a canonical name and its aliases cannot be different.

別名にデータを持たせないから、正規名と別名でデータが食い違いえない。注文表に名前を持たせないから、注文ごとに名前が食い違いえない、と同じ構造です。この規則の詳しい扱いは、別コース『DNSを運用する側から見る』で扱っています。

✓ ここまでの確認

「Codd は1971年の論文で、正規化の目的を更新異常(update anomaly)の防止と書いた」という説明の扱いとして適切なのはどれですか。

⇄ 2つを見比べる
具体例 A

注文表と顧客表

注文表は顧客名を持たず、顧客番号だけを持ちます。名前は顧客表の1行にあり、その行が1つであることを主キー制約が保証します。JOIN で読むたびに、その1か所を見ます。

具体例 B

DNS の CNAME

別名はアドレスを持たず、正規の名前だけを指します。RFC 1034 §3.6.2 は、別名にほかのデータを置かないことで、正規名と別名のデータが食い違わないようにすると書きます。

表の設計と名前解決。領域は違いますが、食い違いを防いでいる共通の手は何でしょうか。

「直すとき、何か所を直す必要があるか」を両方で数えてみてください。

この章のまとめ

  • 書き写した名前を1行だけ直すと、同じ顧客に名前が2つ残る。DB は言われたとおりに保存しただけ。
  • 正規化は、その事実をキーに変える。顧客番号を主キーにすれば「行は1つ」を DB が守り、名前の置き場所も1つになる。
  • 正規化の目的を「update anomalies の防止」と書くのは Kent 1983 で、Codd 1971 の語は dependencies。第1正規形の言い方は資料ごとに違う。
  • Kent は、性能要件を考慮するなら完全に正規化する義務はないと書く。写しを持つなら、そろえる責任も持つ。

このコースでは、行の並びも、全部か無しかの範囲も、一意性も、宣言したときにだけ成り立つことを見てきました。今回の正規化も同じです。データベースが守るのは宣言されたことだけです。 だから設計は、守らせたい事実を宣言できる形に置くことから始まります。

次に読むコース『データベースの仕組み』では、こうして宣言した約束をデータベースが内部でどう果たしているのかを見ていきます。消したはずの行がなぜ残るのか、落ちても壊れないのはなぜか、同時に触られても平気なのはなぜかが題材です。

自分の現場で確かめる

読んだだけでは身につきません。手元の環境で実際に見てみてください。

理解度チェック

読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。

問題 1 / 3正解: 0

Kent 1983 は、完全に正規化することについて何と書いていますか。

用語集(3語)
関数従属
列Aの値を決めると列Bの値が1つに決まる関係。Codd 1971 は「どの時点でも、Aの各値に対応するBの値が1つ以下」と定義する。
正規化・正規形
表の組み方の段階的な指針。第1〜第3正規形は Codd 1970・1971 で導入された。
更新異常
同じ事実の写しが複数あるとき、一部だけを直すと食い違いが残る問題。Kent 1983 は正規化の目的を update anomalies の防止と書く。Codd 1971 の語は dependencies。

出典(一次ソース)