昨日も書いた記事なんですが、基本的に実装にしても設計にしてもこれが最強っていう手法はなくて(あったとしたら全員がそれを使うのでそもそも選択肢という概念がなくなる・・)メリットデメリットをしっかりと考慮するのが大事ではないかと思います。
なので、テーブル定義に関してもその点に関する制御などを。
Contents
サロゲートキー
いわゆる自動採番のキーになります。自然キー(会員番号)などで代理できない場合にこれらを使いがちです。
メリット
- リレーションが簡単になり、intが多いため高速になることが多い。
- 簡便に発行できる(大体のDBベンダーで内蔵されていることが多い)
デメリット
- 業務的に意味を持たないカラムを持たせることになる(ユーザーが識別する番号としては不適切)。
- intだとものすごい列の場合、使い切ってしまうことが多い。
- テスト時に完全に環境移行しようと思った場合に、完全な状態の再現が難しい
使い所
ユーザーが目にしないデータ(not会員番号)とかでない、サブ(子供テーブル)的なテーブルを作るときは基本的にサロゲートキーでいい気が・・・
削除フラグ
データを物理的に残すか、論理的に残すか。
メリット
- データを物理的に残したまま、削除扱いにできる。(何かあったときの予防策)
- テスト時に一時的にデータを消すことができる。
デメリット
- SQLの際に、常に削除フラグ=0のことを忘れないようにしないとダメ(フレームワークなら内蔵されている関数なら自動で弾いてくれる機能あり)
- データ量が増えまくる
- 本来削除フラグを入れるデータは必要のないデータ。一時的に消すのであればステータスなどのフラグで代用すべき
使いどころ
- データが増えすぎないテーブル(マスタ系あるいはそれに準じるもの)の場合(テスト時に便利) どこかで消した方がいいかも・・・
横持ち
本来リレーションで持つべきカラムを横持ちにすること
メリット
- JOINが不要になるためSQLが高速になる
デメリット
- レコードにより不要なカラムが増える(不動産物件などの同型の属性が多いシステムの場合、数百になりがち)
- レコードごとに使うカラム、使わないカラムが出てきやすい
- 属性１、属性２、属性３・・・などの汎用カラムと組み合わせると訳がわからなくなる
使いどころ
一覧系でよく参照され、つけるカラムが少ない場合は使っても害が少ない。多すぎる場合は、基本的に他テーブルにしたほうが良い
Not NULL制約
説明は不要だと思いますが、NULLの不許可。
メリット
- ブランク的なSQLが簡便になる(is not null and <> “”みたいな書き方をしなくて良くなる)
- パフォーマンスが向上しやすい(Nullだとインデックスが効かない)
- NULLがないことで集計や検索が簡便になる
デメリット
- 必須でないところの場合、INSERT時、思わぬ不整合が起きやすい(適切なdefault値や空白をinsertするのを忘れないようにしないといけない)
使いどころ
主に必須系のカラムに対しては必ずこれをつけるべき。
外部キー
外部キー制約です。ちなみに参照する側はNull自体は許可されています。
メリット
- データ的に不整合がおこる可能性を除去できる
デメリット
- 融通が効かない(一時的に処理の整合を崩したい時に使えない)
- 子テーブルを別のカラムにも紐づけたいなどのケースで結局外すことになる
- プログラム側で処理の制約(削除の順番など)を受ける
- テスト時に一時的にデータを消したい・・・などの時にこれがネックでテストが進まないことがある
使いどころ
処理がそれほど複雑でない場合のテーブルの親子テーブルなど
Blob型,JSON型
主にkey=>value系のデータをシリアライズさせてデータを入れる手法。
メリット
- カラム構造が非常にシンプルになる
- 大量のデータを入れておくことができる
デメリット
- インデックスが貼れない
- 検索などが面倒(JSON型はできることもある)
使いどころ
一時補完的な大量データ(一度あったリクエストを覚えておきたい)の保存や検索などをしない複雑な帳票の大量データを入れておきたい時など。
カンマ区切りのデータ
横持ちに近いですが本来リレーションで持つべきところをカンマ区切りで持たせる場合
メリット
- 設計構造を変えずに１つの値から複数の値を持たせることができる(SQLに微細な修正が必要)
デメリット
- インデックスを効かせることができない
- 一般的な検索が難しくなる
- カンマが値の中に入っている場合不可能
使いどころ
複雑になることが予想されないカラムで値を増やしたい時
datetime vs timestamp
メリット、デメリットよりは用途で
datetime
単なる日付の格納。一般的に意味のあるデータを日付として入れたい場合はこちらを使うことになる
timestamp
タイムゾーンの制約を受ける、メモリがdatetimeより少ない、作成日時、更新日時を入れておきたい時に使える。時差も考慮して日付を入れたい時は日付の値にこれを入れる必要があるかも。2038年以降の値が格納できない。一般的にはtimezoneを明示して、こちらを使うべき。
浮動小数点、文字列、decimal
浮動小数点数
誤差が出るので、金額計算では使えない。どの言語でもサポートされており、値がそれほど大きくなく、計算などをせず保存などで使いたい時。積極的に使わない方が良い。
文字列
誤差は出ないが値の計算などがしづらい
decimal
小数点以下の計算を正確に行うことができるが、対応してないライブラリもある。小数点が絡む場合の金額は基本的にこれ
