『SQLアンチパターン』という技術書を読んだ話

SQL
2024-06-16

SQLの学習の一環として、『SQLアンチパターン』という本を読みました。

達人に学ぶSQL徹底指南書にあるようなSQL自体の思想を学んで深く使いこなすというよりかは、だいぶデータベースの設計寄りの内容で、論理設計からアプリケーションに関連したアンチパターンと呼ばれる保守性の悪い設計を紹介してあります。（とはいっても、『失敗から学ぶRDBの正しい歩き方』の方がタイトル通りRDBよりの話なので、それと比較したら、SQL寄りの話ではあります）

システム開発というのは、短納期のためにその場しのぎの設計をしたり、長年の度重なる保守によってデータベースの構造が保守しずらいものになったりして、その中でどうしても実務だけだと、何がテーブル設計として良い設計か見えにくい、下手すればアンチパターンをそれがスタンダードな設計だと思い込んでしまう危険性もはらんでいるわけです。

だから、本書を読む意義としては、他人の失敗から学ぶということと、それがアンチパターンだということを正しく理解するが大事で、RDBMSを使用したシステム開発を携わったことがあれば、知見が得られそうです。
ただし、DBの設計に限らないことですが、最良の設計というのは存在しなくて、どこかに何かしらのトレードオフが存在するので、やはりアンチパターンであってもそこにはメリットがあるから、そういう設計をする場合もあるわけで、このメリット・デメリットも注意していきたいところです。

交差テーブルとは

やはりテーブルの論理設計の章に共通して、特に重要なのが「多対多」の関連を表現する交差テーブルでしょう。
※ 関連テーブル、結合テーブル、多対多、マッピングテーブルなど様々な呼び方がありますが、指しているコンセプトは同じです。

テーブルというのはそれ自体で完結するわけではなく、他のテーブルとある種のエコシステムのように関係性を持ちます。その場合多くがユーザーと製品のように「多対多」の関係になるわけですが、そのまま結合すると直積になるため、適切に2つのテーブルの関係を表現するテーブルが必要になります。

正規化も第4正規形、第5正規形というのは関連エンティティにおける話なので、エンティティ同士の関係、つまりテーブル間の関係をいかに理解して、切り分けられるかが適切に設計できるかの鍵になるかと思います。

交差テーブルはわかりやすい言葉で言うと、テーブルとテーブルのパターンを格納する「組み合わせテーブル」です。
以下は、めっちゃシンプルですが、ユーザーと製品の多対多の関係を組み合わせテーブルを用いたものになります。

-- ユーザーテーブル
CREATE TABLE UsersT (
    user_id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL
);

-- 製品テーブル
CREATE TABLE ProductsM (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

-- ユーザー製品組み合わせテーブル
CREATE TABLE UserProductMappingM (
    user_id INT,
    product_id INT,
    PRIMARY KEY (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES UsersT(user_id),
    FOREIGN KEY (product_id) REFERENCES ProductsM(product_id)
);

このDDLのテーブルの関係をER図で表すと以下のようになります。
A5:SQL Mk-2のER図作成機能を使用してます。

交差テーブルの特徴としては次のような特徴があれば、確実に交差テーブルと考えて良いです。

  • テーブルが2つのテーブルを参照する外部キーを持ちます
  • 外部キーのみでテーブルが構成される

各章で参考になったところ

以下、参考になったところを軽く触れておきます。1章〜8章いろんなアンチパターンが述べられていますが、集約すると交差テーブルや従属テーブルを作成して、関係性を扱うテーブルを作成しようということだと思います。

1章　ジェイウォーク（信号無視）

第1章も「複数の値を持つ属性を格納する」という目的で1つのセルに対して、コンマ区切りで配列のように値を格納してしまっています。
1つのカラムのセルに、複数の値を格納しなければいけない場合は、テーブルを別にして、交差テーブルを作成します。

2章　ナイーブツリー（素朴な木）

第2章は Twitter などコメント機能を持つ、データ構造についてがテーマですが、閉包テーブルの章で関係性のみを表現したテーブルを定義しています。
親と子を Comments テーブルに入れ込むのではなく、先祖/子孫の関係を組み合わせパターンとして管理するためだけのテーブルを TreePaths テーブルとして定義していて、関係性を定義している点は交差テーブルと同じです。データとは別に関係性を定義することで、柔軟にノードの関連付けを変更できるメリットもあります。

3章　IDリクワイアド（とりあえずID）

第3章は主キーがテーマですが、交差テーブルというのは、その特性から複合キー（複数の列で構成されたキー）を持ちやすく以下のどちらかを推奨しています。

  • 外部キー列の組み合わせに対して主キー制約を宣言する
  • 少なくともUNIQUEキー制約を宣言する。

7章　マルチカラムアトリビュート（複数列属性）

第7章は、第1章と同じ複数のデータに対するテーマで、こちらはブログにあるタグのように「複数の値を持つ」属性を格納する場合はどうするかという問題です。
第1章とは逆で、こちらは tag1, tag2, tag3 の列を作成するというように、複数の属性を持つ場合もアンチパターンです。
解決策としては、関係を切り離すのが鉄則で従属テーブル作成して、属性値を複数の列ではなく、複数の行に格納しています。
サンプルでは、bug_id, tagを複合キーにしていますが、tagではなく、tag_id として管理することを考えるならば、交差テーブルの検討も必要になるかと。

8章　メタデータトリブル（メタデータ大増殖）

第8章のメタデータトリブルも同様で、解決策として従属テーブルを導入して、言葉通り年々増え続けるデータを年ごとに属性で持つのではなく、行でもつという点では、第7章と本質的には同じだと思います。

5章　EAV（エンティティ・アトリビュート・バリュー）

第5章の EAV(Entity-Attribute-Value) について、ちょっと触れておくと、さすがにサンプルのようにあらゆる属性とその値を JSON の key と value のように attr_name と attr_value で定義しようとする人はいないと思うでしょ？自分も最初はそう思いました、業務を振り返ってみると、見事にEAVを使用しているテーブルってあるんですよね。特にプロパティがとにかく多いシステムで多い気がします。

例えば金額の合計のように5つほどカテゴリがあって、その型が同じ場合で、特に画面上では2次元表で「合計」と「値」を列に持つ場合は迷いやすいです。
こちらも、その合計値を格納しているレコードの単位がその集計カテゴリーの単位になっているならば、「カテゴリ単位」「合計」「値」のカラムで問題なさそうですが、
レコードの単位が「顧客ID」になっているならば、合計は別に持っていた方が良さそうです。

あと、業務で見かけたことがあるのが、項目種別ID(3種類ほど)、項目ID(1つの種別に対して10〜20種類ほど)、項目値の3つを属性にもつテーブルです。ちなみに、種別IDに対しての種別名、項目IDに対しての項目名はExcelの方で管理しています…（イメージとしては、1つのオブジェクトなり、3Dのモデルなどでとにかく異なるプロパティを扱っているということを考えていただければ…）

完全なるEAVの設計ですが、画面上にこれらの計30~50種類ほどの項目をユーザーに入力させて、ただそれを保存・表示しか行わない場合、こういうシンプルな設計の方が個別に属性として用意するようりも理解しやすいというのは少なからずあるのだろうとは思います。当然、柔軟性がないのは本書の通りであります。

その他参考になったもの

3.2.4 で USING の使用
USING 句を使用すると、結合条件を簡潔に記載できます。USING 句と ON句の併用はできません。

-- ON句を使用したJOIN
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- USING句を使用したJOIN
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);


13.5.3 で NULLを含んだ条件判定で簡潔に書ける述語です。

SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;
SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;

ちなみに、Oracle と Microsoft SQL Server など一部の演算子は直接サポートされていないため、以下のような書き方が簡潔になりそうです。（本書サポート時）

-- assigned_to IS NULL OR assigned_to <> 1 の場合
SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;

-- assigned_to IS DISTINCT FROM 1 の場合
SELECT * FROM Bugs WHERE NVL(assigned_to, -1) <> 1;

14.5.4 JOINを使用する場合のサンプルで、相関サブクエリなどを使う場合に比べて、JOINで結合するとその結合条件に対して、テーブルのレコードが絞り込まれるためパフォーマンスの観点でみれば良いです。複数のテーブルを同時に結合し過ぎて、どうなっているのかわからなくなるというデメリットも当然ありますが。

23章の23.5.1 の文書化です。
データーベース関連の文書で、何をドキュメントとして残しておくべきか、バージョン管理ツールに登録すべきか書いてあります。プロジェクトによっては、テーブル定義書として、各テーブルのカラムの物理名や論理名などを残してたりするのですが、これってSQLから簡単に引っ張ってこれるので、文書として価値があるのは、ER図だったり、データベースを説明する文書になります。

具体的には、ER図からは知ることができないテーブルや列などのオブジェクトの目的や利用性、エンティティの分類（参照テーブル、交差テーブル、従属テーブルなど）、列の値の意味が記載してあると良いです。

参照

SQLアンチパターン

タイトルとURLをコピーしました