skillup

技術ブログ

Database ドキュメント作成 プログラミング全般

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

投稿日:

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

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

サロゲートキー

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

メリット

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

デメリット

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

使い所

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

削除フラグ

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

メリット

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

デメリット

  • SQLの際に、常に削除フラグ=0のことを忘れないようにしないとダメ
  • データ量が増えまくる
  • 本来削除フラグを入れるデータは必要のないデータ。一時的に消すのであればステータスなどのフラグで代用すべき

使いどころ

  • データが増えすぎないテーブル(マスタ系あるいはそれに準じるもの)の場合(テスト時に便利)

横持ち

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

メリット

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

デメリット

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

使いどころ

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

Not NULL制約

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

メリット

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

デメリット

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

使いどころ

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

外部キー

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

メリット

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

デメリット

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

使いどころ

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

Blob型,JSON型

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

メリット

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

デメリット

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

使いどころ

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

カンマ区切りのデータ

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

メリット

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

デメリット

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

使いどころ

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

datetime vs timestamp

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

datetime

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

timestamp

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

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

浮動小数点、文字列、decimal

浮動小数点数

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

文字列

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

decimal

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

-Database, ドキュメント作成, プログラミング全般
-

執筆者:


comment

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

関連記事

no image

JPQLでの算術関数

複雑なJPQLを書いていると、通常のレコードの取り出しだけではなく、合計(SUM)や算出(COUNT)などのいわゆる算術関数を使うことが一般的です。 JPQLでもこれらを通常通り扱うことができます。 …

no image

Postgresの基礎(主にMySQLとの違いなど)

えー来月(2020年3月)より、postgresを使うかもしれないので、ちょっと復習を。 Contents1 以前のリンク2 基本コマンド比較2.1 超頻出系3 テーブル比較4 SELECT文5 do …

no image

バッチスクリプトで気をつけたい点

実務でバッチ処理を作る際に気をつけるべきと思ったこと。 基本的にエラーをいかに捉えていかにログに吐くかを最初に考える。まずはエラーありき。失敗するもの、想定した値がこない、あるいは値がないを前提として …

no image

Cakeでのリレーションについて

いまさらながらCakeのリレーションについての復習。 基本から。 Contents1 基本的なリレーション1.1 1対N1.2 N対11.3 動的な紐づけ 基本的なリレーション 下記のようなテーブル構 …

no image

テストのダミーデータ作成

データベースに大量のデータを作りたいときにいつもあああやhoge,aaaですとデータという感じがしないですし、抽出や集計ができません。 なるべく自然に近いデータが欲しいのですが、簡単に作れる方法があり …