skillup

技術ブログ

Database ドキュメント作成

テーブル設計に関するメリデメ

投稿日:2021年12月12日 更新日:

昨日も書いた記事なんですが、基本的に実装にしても設計にしてもこれが最強っていう手法はなくて(あったとしたら全員がそれを使うのでそもそも選択肢という概念がなくなる・・)メリットデメリットをしっかりと考慮するのが大事ではないかと思います。

なので、テーブル定義に関してもその点に関する制御などを。

サロゲートキー

いわゆる自動採番のキーになります。自然キー(会員番号)などで代理できない場合にこれらを使いがちです。

メリット

  • リレーションが簡単になり、intが多いため高速になることが多い。
  • 簡便に発行できる(大体のDBベンダーで内蔵されていることが多い)

デメリット

  • 業務的に意味を持たないカラムを持たせることになる(ユーザーが識別する番号としては不適切)。
  • intだとものすごい列の場合、使い切ってしまうことが多い。
  • テスト時に完全に環境移行しようと思った場合に、完全な状態の再現が難しい

使い所

ユーザーが目にしないデータ(not会員番号)とかでない、サブ(子供テーブル)的なテーブルを作るときは基本的にサロゲートキーでいい気が・・・

削除フラグ

データを物理的に残すか、論理的に残すか。

メリット

  • データを物理的に残したまま、削除扱いにできる。(何かあったときの予防策)
  • テスト時に一時的にデータを消すことができる。

デメリット

  • SQLの際に、常に削除フラグ=0のことを忘れないようにしないとダメ(フレームワークなら内蔵されている関数なら自動で弾いてくれる機能あり)
  • データ量が増えまくる
  • 本来削除フラグを入れるデータは必要のないデータ。一時的に消すのであればステータスなどのフラグで代用すべき

使いどころ

  • データが増えすぎないテーブル(マスタ系あるいはそれに準じるもの)の場合(テスト時に便利) どこかで消した方がいいかも・・・

横持ち

本来リレーションで持つべきカラムを横持ちにすること

メリット

  • JOINが不要になるためSQLが高速になる

デメリット

  • レコードにより不要なカラムが増える(不動産物件などの同型の属性が多いシステムの場合、数百になりがち)
  • レコードごとに使うカラム、使わないカラムが出てきやすい
  • 属性1、属性2、属性3・・・などの汎用カラムと組み合わせると訳がわからなくなる

使いどころ

一覧系でよく参照され、つけるカラムが少ない場合は使っても害が少ない。多すぎる場合は、基本的に他テーブルにしたほうが良い

Not NULL制約

説明は不要だと思いますが、NULLの不許可。

メリット

  • ブランク的なSQLが簡便になる(is not null and <> “”みたいな書き方をしなくて良くなる)
  • パフォーマンスが向上しやすい(Nullだとインデックスが効かない)
  • NULLがないことで集計や検索が簡便になる

デメリット

  • 必須でないところの場合、INSERT時、思わぬ不整合が起きやすい(適切なdefault値や空白をinsertするのを忘れないようにしないといけない)

使いどころ

主に必須系のカラムに対しては必ずこれをつけるべき。

外部キー

外部キー制約です。ちなみに参照する側はNull自体は許可されています。

メリット

  • データ的に不整合がおこる可能性を除去できる

デメリット

  • 融通が効かない(一時的に処理の整合を崩したい時に使えない)
  • 子テーブルを別のカラムにも紐づけたいなどのケースで結局外すことになる
  • プログラム側で処理の制約(削除の順番など)を受ける
  • テスト時に一時的にデータを消したい・・・などの時にこれがネックでテストが進まないことがある

使いどころ

処理がそれほど複雑でない場合のテーブルの親子テーブルなど

Blob型,JSON型

主にkey=>value系のデータをシリアライズさせてデータを入れる手法。

メリット

  • カラム構造が非常にシンプルになる
  • 大量のデータを入れておくことができる

デメリット

  • インデックスが貼れない
  • 検索などが面倒(JSON型はできることもある)

使いどころ

一時補完的な大量データ(一度あったリクエストを覚えておきたい)の保存や検索などをしない複雑な帳票の大量データを入れておきたい時など。

カンマ区切りのデータ

横持ちに近いですが本来リレーションで持つべきところをカンマ区切りで持たせる場合

メリット

  • 設計構造を変えずに1つの値から複数の値を持たせることができる(SQLに微細な修正が必要)

デメリット

  • インデックスを効かせることができない
  • 一般的な検索が難しくなる
  • カンマが値の中に入っている場合不可能

使いどころ

複雑になることが予想されないカラムで値を増やしたい時

datetime vs timestamp

メリット、デメリットよりは用途で

datetime

単なる日付の格納。一般的に意味のあるデータを日付として入れたい場合はこちらを使うことになる

timestamp

タイムゾーンの制約を受ける、メモリがdatetimeより少ない、作成日時、更新日時を入れておきたい時に使える。時差も考慮して日付を入れたい時は日付の値にこれを入れる必要があるかも。2038年以降の値が格納できない。一般的にはtimezoneを明示して、こちらを使うべき。

【MySQL】DATETIMEとTIMESTAMPの違いや2038年問題などについて解説。

浮動小数点、文字列、decimal

浮動小数点数

誤差が出るので、金額計算では使えない。どの言語でもサポートされており、値がそれほど大きくなく、計算などをせず保存などで使いたい時。積極的に使わない方が良い。

文字列

誤差は出ないが値の計算などがしづらい

decimal

小数点以下の計算を正確に行うことができるが、対応してないライブラリもある。小数点が絡む場合の金額は基本的にこれ

-Database, ドキュメント作成
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

仕様の把握で見るポイント

新しい現場に入って技術的な部分はもとより仕様の把握などでポイントになる点などを。 Contents1 ER図2 ステータス変更3 プレイヤー(イベント)整理4 タイムテーブル5 マトリックス表6 ダミ …

no image

mavenのリモートリポジトリについて

JPAでO/Rマッパーに慣れてからというもの通常のSQLをごりごり書くのが億劫になってきました。 億劫というかいろいろとリスクがありますね。 問題点としてはコンパイルするときにエラーが検知できなかった …

no image

MySQLのLIMIT,OFFSETに関して&explainの見方など

自作のWEBアプリを作っていたところSELECT句が異常に遅いケースがありました。 発見までにかなり時間がかかったんですが、不可思議な現象としてはOFFSETが小さいときと大きいときで検索スピードが全 …

no image

MySQLのユーザー変更+information_schema.columns

MySQLで行うユーザーの作成について

これですが、一つのデータベースに対して行うとhost内のユーザーすべてが切り替わってしまいます …

no image

EXISTSと集合

前回に引き続きEXISTSの問題です。 下記のようなテーブル(projects)があり、 project_id | step_nbr | status ———&#8 …

アーカイブ