テーブル設計のアンチパターン３選

テーブル設計のアンチパターンを3つ紹介します。
著者は現状SAPのエンジニア(ABAPer)です。
以下は主にABAPでのアドオン開発での経験をベースに記載していますが、スクラッチ開発でも当てはまると思います。

# 正規化されていない
正規化とは、**データの重複を無くして1つの事実を一箇所に保存するようにテーブルを設計する事、です**。
正規化されていないテーブルは、DB設計の典型的なアンチパターンです

次の要件のテーブルがあったとします。

>要件：商品コード、商品名、価格、商品グループコード、倉庫別の最小安全在庫を保持する。

正規化されていないテーブルとは、次のような設計になっています。

 * 商品マスタテーブル

| #  | 商品コード(key) | 倉庫コード(key) | 商品名     | 販売価格 | 商品グループコード | 最小安全在庫 |
|----|-----------------|-----------------|------------|----------|--------------------|--------------|
| 1  | I001            |                 | プリンター | 10000    | P1                 |              |
| 2  | I001            | WH001           |            |          |                    | 40           |
| 3  | I001            | WH002           |            |          |                    | 50           |
| 4  | I001            | WH003           |            |          |                    | 30           |
| 5  | I002            |                 | パソコン   | 120000   | P1                 |              |
| 6  | I002            | WH001           |            |          |                    | 10           |
| 7  | I002            | WH002           |            |          |                    | 20           |
| 8  | I002            | WH003           |            |          |                    | 25           |
| 9  | I003            |                 | 冷蔵庫     | 140000   | K1                 |              |
| 10 | I003            | WH001           |            |          |                    | 3            |
| 11 | I003            | WH002           |            |          |                    | 4            |
| 12 | I003            | WH003           |            |          |                    | 5            |

倉庫コードがNULLとなっているレコードが、商品の情報を保持しています。
最小安全在庫のみ、商品コードと倉庫コードの組み合わせに従属しています。

これは、データベース用語でいう第一正規形というものです。
１つのテーブルに商品コードに紐づく項目(商品名、価格、商品グループコード)と商品コードと倉庫コードの組み合わせに紐づく項目(最小安全在庫)
が混在しています。
テーブルを設計した後にお客さんからの仕様変更に基づいてテーブルの値とキーを追加(今回のケースだと倉庫コードと最小安全在庫)した場合に、こういうテーブルになる場合が多いです。

この状態がなぜ良くないというと例えば次のようなデータも登録できてしまうのです。

 * 商品マスタテーブル

| # | 商品コード(key) | 倉庫コード(key) | 商品名     | 販売価格 | 商品グループコード | 最小安全在庫 |
|---|-----------------|-----------------|------------|----------|--------------------|--------------|
| 1 | I001            |                 | プリンター | 10000    | P1                 | 10           |
| 2 | I001            | WH001           | Printer    |          |                    | 40           |
| 3 | I001            | WH002           |            |          |                    | 50           |
| 4 | I001            | WH003           |            |          |                    | 30           |

こうなると、例えば
1行目に最小安全在庫10個とあるけれど、これってどの倉庫の最小安全在庫なの？
とか、
1行目と2行目にI001の名称がプリンター、Printerとあるけど、どちらが正しいの？
というような事になります。
このように1つの事実は1箇所にのみ登録するというデータベースの思想に反する事ができてしまうのです。

このような指摘をすると、「このテーブルはシステム担当者しか扱わないし、正しく運用していれば大丈夫だ」みたいに考えられる方もいらっしゃいます。
ですが、正しい運用を続けるにはテーブルの使い方を定義したドキュメントを残したり、運用担当が正しく引き継がないといけません。
ファイルサーバーがぶっ飛んでドキュメントが失われたらどうしますか？

手の込んだ運用をドキュメントで引き継ぐよりも、データベースのセオリーに従った設計をする方が得策です。

こういうテーブルは、次のようにテーブルを分けて（正規化して）1事実1箇所となるようにしましょう。

 * 商品マスタテーブル

| # | 商品コード(key) | 商品名     | 販売価格 | 商品グループコード |
|---|-----------------|------------|----------|--------------------|
| 1 | I001            | プリンター | 10000    | P1                 |
| 2 | I002            | パソコン   | 120000   | P1                 |
| 3 | I003            | 冷蔵庫     | 140000   | K1                 |

 * 商品在庫マスタテーブル

| # | 商品コード(key) | 倉庫コード(key) | 最小安全在庫 |
|---|-----------------|-----------------|--------------|
| 1 | I001            | WH001           | 40           |
| 2 | I001            | WH002           | 50           |
| 3 | I001            | WH003           | 30           |
| 4 | I002            | WH001           | 10           |
| 5 | I002            | WH002           | 20           |
| 6 | I002            | WH003           | 25           |
| 7 | I003            | WH001           | 3            |
| 8 | I003            | WH002           | 4            |
| 9 | I003            | WH003           | 5            |

# 不要な項目をキーに含めてしまう。
キーに不適切な項目を含めている事でバグが出やすくなっているテーブルもたまに目にします。
これは正規化されていない事とちょっとかぶります。

例えば以下のようなテーブルです。
>要件：各種OSの繋がり(どのOSが次のバージョンか)と、現行の最新OSを管理する

* OSバージョン管理マスタ

| # | 旧OS(key)         | 新OS(key)         | リリース時期 | 現行OSフラグ |
|---|-------------------|-------------------|--------------|--------------|
| 1 | macOS Sierra      | macOS High Sierra | 2015/5/1     |              |
| 2 | macOS High Sierra | macOS Mojave      | 2016/8/1     |              |
| 3 | macOS Mojave      | macOS Catalina    | 2019/2/1     | X            |
| 4 | macOS Catalina    |                   | 2021/9/1     |              |

（リリース時期とか適当です。）
このテーブルがなぜまずいのかというと、旧OSと真OSの両方がキーとなっているため、1つの旧OSに対して複数の新OSを登録できてしまうのです。
例えばこんな感じです。

* OSバージョン管理マスタ

| # | 旧OS(key)    | 新OS(key)         | リリース時期 | 現行OSフラグ |
|---|--------------|-------------------|--------------|--------------|
| 1 | macOS Sierra | macOS High Sierra | 2015/5/1     | X            |
| 2 | macOS Sierra | macOS Mojave      | 2016/8/1     |              |

こうなると、「macOS Sierraの次のOSはmacOS High SierraなのmacOS Mojaveなの？」って話になります。

こうしないために、1つの旧OSに対して複数の新OSが登録できないようにアプリ側の登録機能で制限をかけるコードも見かけるのですが、
**そんなことをするくらいなら初めから新OSをキーにしなければ良い**話です。

このような事にならないためにも、テーブルを設計する時は**「何の単位で情報（レコード）を管理したいのか」**をよく検討する必要があります。
この場合、旧OSを単位とすべきなのは明らかでしょう。

# ログテーブルが存在しない。
これはテーブルというよりアプリの設計の話なのですが、**特にマスタやカスタマイズのテーブルは変更履歴が残るようにログテーブルを作っておいたほうが良い**です。

ログテーブルの設計方法はいろいろあると思います。
以下のリンクが、大変参考になります。

https://qiita.com/ak-ymst/items/2e8e92f212c807bb09a1

以下に挙げる例はテーブルの主キープラス最終更新日と最終更新時間をキーとしたログテーブルです。
(リンクで言う、"その2"に該当するかな？)

 * 商品マスタテーブル

| # | 商品コード(key) | 商品名     | 販売価格 | 商品グループコード | 登録日   | 変更日   |
|---|-----------------|------------|----------|--------------------|----------|----------|
| 1 | I001            | プリンター | 15000    | P1                 | 2020/3/1 | 2020/3/6 |

 * 商品マスタログテーブル

| # | 商品コード(key) | 最終更新日(key) | 商品名     | 販売価格 | 商品グループコード | 登録日   | 変更日   |
|---|-----------------|-----------------|------------|----------|--------------------|----------|----------|
| 1 | I001            | 2020/3/3        | プリンター | 13000    | P1                 | 2020/3/1 | 2020/3/3 |
| 2 | I001            | 2020/3/1        | Printer    | 10000    | P1                 | 2020/3/1 |          |


こういうテーブルにしておくと、「商品：プリンターは3/1に登録した時は価格10000円だった。3/3に13000円に変更された。3/6にさらに15000円に変更された。」
という流れが読み取れるようになります。

こういう設計にしておかないと、ユーザーから「昨日価格を13000に更新したはずなのになぜか15000円で登録されています。」みたいな問い合わせを受けた時に調査できなくなってしまいます。
特に多人数が使う業務系のシステムだと、こういうログテーブルがないと問題となりやすいです。
また、システム監査で指摘を受ける可能性もあります。


# まとめ
テーブルの設計は運用してしまうとなかなか変えられません。データの移行がめんどくさいし、お客さんにとってメリットを訴求する事が難しいからです。
だからこそ、システムの設計の段階で、上記のようなセオリーに沿った正しいテーブルにしておいた方が良いです。

よかったら参考にしてください。