エンジニアの格闘

エンジニアのみなさんはかつてひどいコードや設計と直面し、それと格闘したことでレベルアップした経験はあるでしょう。
つまり、先輩エンジニアたるものクソコードやクソ設計を残して、後輩エンジニアのレベルアップに寄与するのは義務だと言っても過言ではありません(?)

今回はDB設計に焦点をあてて、そのように絶望させる設計の残し方を記しておきます。
初めての投稿なのでレベル的にはかなり初歩になっています。

↑きっと彼も立派なエンジニアになった時感謝してくれるでしょう

1) 必要な正規化を行わない

エンジニアという不思議な不思議な生き物は処理の共通化等なにかと処理をまとめたがる習性があります。
以下のように著者テーブルと書籍テーブルがあるとします。
書籍

書籍ID	書籍名	著者ID
1	モフモフ牧羊犬	1
2	シュッとした牧羊犬	2
3	とても賢い牧羊犬	2

著者

id	著者名
1	シェルティ
2	ボーダーコリー
3	ゴールデンレトリバー

でもこれってなんかひとつのテーブルにまとめられそうですよね。
だって著者と作品って基本ワンセットの情報ですもん。

書籍ID	書籍名	著者ID	著者名
1	モフモフ牧羊犬	1	シェルティ
2	シュッとした牧羊犬	2	ボーダーコリー
3	とても賢い牧羊犬	2	ボーダーコリー
4	書籍未発売	３	ゴールデンレトリバー

何がおきるか

著者の名前を変更する場合、その著者が書いたすべての書籍の行を更新する必要がでてくる
新しい著者を追加する場合、その著者が書いた書籍がないと、その著者をデータベースに追加することができないのでデータの柔軟性がなくなる
→　最悪「書籍未発売」等の値を入れることで書籍を発売していない著者も無理矢理登録できる。しかし、その後「書籍未発売」という書籍を発売する著者が現れる可能性もあり、データの整合性を保つのに苦労する。 クソ設計にはクソ運用がつきものである。
↑の運用をした場合でも、今後ゴールデンレトリバーさんがが書籍を発売した場合、仮で入れたレコードを削除もしくは修正する必要がでてくる
例えば「モフモフ牧羊犬」の内容に不適切な箇所があり該当書籍を削除すると、シェルティさんは他の書籍を発売していないためDBからシェルティの情報が失われる

正規化はDB設計の初歩の初歩ですが、だからこそこれらを適切に行わないことは致命的な効果を生むことができます。

2) 自由なDBを作るため制約を取り払う

エンジニアという生き物ははどこかの主人公のようにのように自由を求める生き物です。
リモートワークにフレックスなど。。
きっとそれはDBについても同じなのでデータを縛る制約なんて取り払ってあげましょう。

おかげで以下のような~~無法地帯~~夢のDBを作ることができました

ユーザーテーブル

ユーザーID	ユーザー名	email	誕生日
1	コナン	shinichi@beika.com	1991/5/4
2	服部	***@beika.com	1991/8/10
3	安室	dummy	2112/9/3

商品テーブル

商品ID	商品名	価格
aaa	麻酔針	1
bbb	小麦粉	100
ccc	コーヒー豆	50
bbb	ボール射出ベルト	5000

注文テーブル

注文ID	ユーザーID	商品ID	注文数	合計金額
1	1	aaa	999	999
2	2	bbb	-10	-1000
3	3	ccc	10	500
4	４	bbb	1	100

何がおきるか

誕生日が未来の日付を許容してしまっており、今後誕生日を参照して現在の年齢を算出するコードが実装されればバグる
注文テーブルに存在しないユーザーの注文を格納できてしまい、謎のデータを作成できてしまう
商品テーブルのIDを被らせることで注文内容の整合性を消すことができ、発覚したときの調査にが必要になる
安室さん謎のアドレスを登録できてしまっている。
注文数の数値に負の値を許容することで合計金額を狂わせることができる。しかし、これは 実は返品処理を表したレコードであるというトラップを仕込む こともできる。後任はその辺りの仕様も調査しなければならない

自由とは責任を伴うものです。先に自由の弊害を教えてあげるのも先輩の役目でしょう

3) 過程を抹消する

消費税率や通貨レート等、時期によって数字が変動するものは当時の値を残しておかないと後から参照できなくなります。
ここはあえてそれら途中経過を握りつぶことで絶望させてあげましょう。
テーブルに保存するレコードも少なくなって一石二鳥ですね。

注文管理

注文ID	購入者	購入通貨ID	購入日時
1	ジャビット	1	2023-5-1 00:00:00
2	トラッキー	2	2023-5-2 00:00:00
3	ドアラ	3	2023-5-3 00:00:00

通貨管理

ID	通貨名	現在のレート	レート更新日時
1	ビットコイン	100	2023-5-24 00:00:00
2	イーサリアム	200	2023-5-24 00:00:00
3	ネム	300	2023-5-24 00:00:00

何がおきるか

現在のレートしか保存していないため、例えば過去の注文がおかしいことが発覚した場合、当時のレートを検索するのは困難になる。

集計.sql

select SUM(通貨管理.現在のレート) from 注文管理
INNER JOIN 通貨管理 ON 注文管理.購入通貨ID = 通貨管理.ID
WHERE 通貨管理.購入通貨ID = 1
GROUP BY 通貨管理.購入通貨ID

↑のように後から集計しようとしても全て現在のレートで計算されてしまう

過去の取引等を調査する際、絶望して立ち尽くす後輩の姿が目に浮かびますね。

対処方法

一応どうすればよいか、一つの例を記載しておきます。
今回の例だとそのレートの有効日を入れるカラムを追加し、後から集計する場合等はその注文日にひっかかるレコードを検索して集計すれば後からの調査が行いやすくなります

1	2	3	4	5
ID	通貨	レート	開始時間	終了時間
1	ビットコイン	100	2023-5-23 00:00:00	2023-5-23 23:59:59
2	ビットコイン	120	2023-5-24 00:00:00	2023-5-24 23:59:59
3	イーサリアム	200	2023-5-23 00:00:00	2023-5-23 23:59:59
4	イーサリアム	220	2023-5-24 00:00:00	2023-5-24 23:59:59
5	ネム	300	2023-5-23 00:00:00	2023-5-23 23:59:59
6	ネム	320	2023-5-24 00:00:00	2023-5-24 23:59:59

4) 徹底的に正規化する

正規化を行うことはデータの一貫性を守るために非常に大事です。
そこで、これでもかというくらい正規化してあげて、データを守ってあげましょう

以下のように適切に正規化されたテーブル群があったとします

学生

学生ID	名前	メールアドレス
1	野比	nobi@example.com
2	出来杉	dekisugi@example.com
3	剛田	goda@example.com

コース

コースID	コース名	教師名
1	数学	骨川
2	物理	源
3	化学	猫型ロボット

学生・コース

学生ID	コースID
1	1
1	3
2	1
3	3

でもなんかもっと正規化できそうじゃないですか？限界を超えて正規化しましょう。

学生

学生ID	名前
1	野比
2	出来杉
3	剛田

アドレス

学生ID	メールアドレス
1	nobi@example.com
2	dekisugi@example.com
3	goda@example.com

コース

コースID	コース名
1	数学
2	物理
3	化学

教師

コースID	教師名
1	骨川
2	源
3	猫型ロボット

学生ID	コースID
1	1
1	3
2	1
3	3

何がおきるか

例えば猫型ロボット先生の受け持っている授業,受けている生徒の名前とアドレスを抽出するクエリを作成しようと思ったら以下のように結合するテーブルが多くなります。

適切な正規化.sql

select コース.教師名, コース.コース名, 学生.学生名, 学生.メールアドレス from 学生・コース
INNER JOIN 学生 ON 学生・コース.学生ID = 学生.学生ID
INNER JOIN コース ON 学生・コース.コースID = コース.コースID
WHERE コース.教師名 = "猫型ロボット"

過剰な正規化.sql

select 教師.教師名, コース.コース名, 学生.学生名, アドレス.メールアドレス from 学生・コース
INNER JOIN コース ON 学生・コース.コースID = コース.コースID
INNER JOIN 教師 ON 学生・コース.コースID = 教師.コースID
INNER JOIN 学生 ON 学生・コース.学生ID = 学生.学生ID
INNER JOIN アドレス ON 学生・コース.学生ID = アドレス.学生ID
WHERE 教師.教師名 = "猫型ロボット"

正規化はデータの一貫性を維持するためには良いことですが、このように過剰に行うと効率性を損ないます

このような設計と戦う後輩は複雑なクエリを組むスキルが高まることでしょう。感謝してほしいですね。

まとめ

エンジニアは困難を乗り越えて成長する

僕は勘弁ですけどね