テーブル設計のアンチパターンを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を単位とすべきなのは明らかでしょう。
ログテーブルが存在しない。
これはテーブルというよりアプリの設計の話なのですが、特にマスタやカスタマイズのテーブルは変更履歴が残るようにログテーブルを作っておいたほうが良いです。
ログテーブルの設計方法はいろいろあると思います。
以下のリンクが、大変参考になります。
以下に挙げる例はテーブルの主キープラス最終更新日と最終更新時間をキーとしたログテーブルです。
(リンクで言う、"その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円で登録されています。」みたいな問い合わせを受けた時に調査できなくなってしまいます。
特に多人数が使う業務系のシステムだと、こういうログテーブルがないと問題となりやすいです。
また、システム監査で指摘を受ける可能性もあります。
まとめ
テーブルの設計は運用してしまうとなかなか変えられません。データの移行がめんどくさいし、お客さんにとってメリットを訴求する事が難しいからです。
だからこそ、システムの設計の段階で、上記のようなセオリーに沿った正しいテーブルにしておいた方が良いです。
よかったら参考にしてください。