この記事ではテーブルの設計でよくあるパターンを解説します。
正規化を崩した方が使いやすかったりする事があります。
アンチパターンは経験によるところが多いです。この記事を読めば理由がわかるようにしましたので、実践できっと役に立ちます。
筆者はOracle ERPのテーブルに精通しています。ERPは会社の業務システムのほとんどのフローが入っているシステムです。設計上のパターンの多くを知っているのでそれを解説するよ。
データの削除は基本はしません。
削除したい場合は、
・削除フラグの項目を追加する
・有効日と失効日の項目を用意する
のどちらかか両方をして、項目のデータで削除状態を区別します。
有効日と失効日はこのようなテーブルです。
今日は2020-06-01だった場合、田中さんは有効データで、中村さんは削除扱いのデータとなります。
確定前のトランザクションデータは削除を許可していることが多いです。
注文データでしたら、注文データが社内で承認される前。
受注データでしたら、受注確定の処理がされる前までの状態のデータです。
ERPでは特にそうですが、一度データが確定するといろいろな業務システムにデータを送信します。どのシステムにもデータを送信していない状態というのが、削除の絶対条件です。
上が電話番号の項目を横にした持ち方。
下は柔軟性を持たせた持ち方です。
中小規模のシステム
項目を横にした持ち方の方が多いです。
・処理を作るうえでシンプル
・必要な電話番号のパターンがある程度絞り込める
という理由です。
大規模システム
電話番号テーブルを分けるケースが多いです。理由は電話のカテゴリを柔軟に増やせるからです。
テーブルを作った時は、何も考えずにこの４つの項目は追加してください。
・作成日
・作成者
・更新日
・更新者
データの問題やシステム上の問題でトラブルがあった時に、調査でよく使います。
「作成日」、「更新日」についてはTriggerで自動保存する仕組みを検討した方がいいです。他システムにデータ連携する時「更新日」をよく使います。
プログラム上で更新日を変更する設計にすると、漏れが出てくることがあります。
データ連携に関しては、稼働当初は全データを連携をすることが多いです。5年とか10年たちデータ量が多くなるとパフォーマンスの問題が発生して、更新したデータだけに切り替えていくのがよくあるパターンです。
更新日がたまに変更されていなくて、行き詰る事があります。
社員情報テーブルを考えてみます。
この情報だけ見ても、２つのパラメータファイルが必要となります。
このようにパラメータテーブルを作っていくと何百ともテーブルが必要になります
そのような場合は共通のパラメータテーブルを用意します。
「社員タイプテーブル」と「勤務地テーブル」の内容を共有して、「パラメータテーブル」を持ちます。２つの内容はパラメータ区分の項目で区別します。
パラメータテーブルの共有はおすすめです。
・パラメータテーブルを作る手間が省ける
・パラメータテーブルの名前を覚える手間が省ける
・メンテ画面を共有できる
注文情報の例でみてみます。
主キーは連番を使う
「注文書ヘッダ」テーブルの注文番号はキーですが、別に数値型の主キー項目「PO_ID」を追加します。
理由は３つあります
・数値型の１項目の方がテーブルの結合が早く、パフォーマンスがいい。
・主キーが、主キーではなかった時に大きなプログラム修正が必要となる。
・テーブルを結合する時に理解しやすい。注文書ヘッダの項目を見て、どれが主キーだったかなと考える必要がない
初見ですと意味のない連番より「注文番号」などの意味ある項目を使った方がわかりやすく見えます。ですが、意味ありの項目を使うと主キーの思い違いなどでトラブルの危険性が高くなります。
主キーにはわかりやすい名前を使う
たまにあるのは、テーブルの主キーの名前をIDで固定しているケースです。これはテーブルを結合するときに、わかりにくくバグの確率があがるので、きちんと意味がわかる名前にしましょう。
今回は５つ、テーブル設計時に注意すべき点をあげました。
テーブル設計一つで、開発効率をよくしたり、バグの発生頻度を少なくできます。
今まで多くのシステムのテーブル設計を見て、その知見を元にあげましたので、参考にしてください。
・データの削除はしない
・繰り返し項目の持ち方はケースバイケース
・作成日と更新日の項目は必ず追加する
・パラメータは共通のテーブルを用意する
・主キーは連番を使いわかりやすい名前をつける