skillup

技術ブログ

Database

論理設計のグレーノウハウ 列持ちテーブル、集計キー、多段ビュー

投稿日:2016年8月3日 更新日:

前回に引き続き論理設計のグレーノウハウについて。

列持ちテーブル

これは下記のようなテーブルです。

社員ID 社員名 子1 子2 子3
001 山田 一郎 二郎 三郎
002 鈴木 健太 寛太

列持ちとはこのように子1~子3のようなテーブルを持つことですね。比較的よくやりますね。

今の開発でもたまに使っています。とりあえずメリットとデメリットを見ていこうと思います。

メリット

シンプルな設計

見たまんまで非常にわかりやすいですね。正規化とかスカラを理解していなくてもOKです。

入出力のフォーマットと合わせやすい

シンプルさゆえに画面とロジック、データベースとの連携が非常に簡単です。CSVで出力するときもこのまま出力すればOKですし、SELECT文でだせばいいだけです。

デメリット

列の増減が難しい

リレーショナルテーブルの欠点として後になってからの構成変更が難しいということです。列自体が不要になったり、増加させたい場合に変更コストが大きくなります。

無用のnullが必要

列持ちテーブルの欠点として、子がいない、子が少ない場合は無用のnullを使う必要が出てくるということです。

nullが含まれているとSQL文の結果を混乱させるもとになります。

特殊な状況でなければ別途行持ちテーブルを作るべき

そのため使いどころによっては有用ですが、そうでなければなるべく下記のような行持ちテーブルにすべきです。

社員ID 枝番
001 1 一郎
001 2 二郎
001 3 三郎
002 1 健太
002 2 寛太

アドホックな集計キー

これは下記のようなテーブルがあり、地方ごと(北海道・東北、関東、中部、・・)の人口を出したいという要件があったとします。

都道府県コード 都道府県名 人口(万人)
001 北海道 550
002 青森 130
022 静岡 370
023 愛知 740

このときに地方コードとして新たに北海道・東北=01、関東=02、東海=03を取り入れるというような設計を見かけます。

確かにこのような列を新たに追加すればSQLの集計が非常に楽になります。半面、こういったアドホック(場当たり的)な集計キーは名前の通り、短いスパンでコードが変わったり、別のコード体系が必要になったりします。

このようなことを繰り返すとますますテーブルが巨大になり、パフォーマンスが落ちる原因になります。

このようなアドホックな列を作る代わりの対策としては下記のようなものがあげられます。

対策1 キーを別に分離

都道府県コードと地方コードだけをもったテーブルを別に作ることで、通常のテーブルよりも工数は小さくなります。ただし、結合処理が必要になるのでパフォーマンス問題の解決にはあまり寄与しません。

対策2 ビューを作成

これはオリジナルのテーブルには手を加えず、地方コードを追加したビューを用意して、そのビューへのアクセスによって簡単にSQLが記述できます。

ただし、後述するような多段ビューという問題が発生します。

対策3 SQLの中で記述

これはGROUP BY句の中でアドホックキーを作ってしまう方法です。

具体的には下記のようにSQLを書きます。

多段ビュー

ビューには抽出したい列のみを取り出せる、複雑なSQLの一部を定義することでSQLをすっきり表現できるなど数々のメリットがあります。

しかし当然ながらすべての技術にはメリットとデメリットがあり、ビューを作りすぎてしまうことのデメリットも当然発生します。

以下のようなことがデメリットになります。

デメリット

実テーブルとの差分

これは先輩からよく聞かされました。元のテーブルとの差分ができてしまうということで作っても変更や維持コストがそれなりにかかるということなんですね。

パフォーマンスの劣化

ビューは物理的にテーブルを所持しているのではなく、ただのクエリです。そのため何段にもわたるようなviewを作成していれば依存関係が複雑になり、わかりにくくなりますし、パフォーマンス自体も劣化します。

ビュー自体は一段にとどめておくようにしましょう。

システムの世界には過度に複雑なつくりはシステムをダメにするという思想があるようです。

参考

Road to Oracle Master ~オラクル・マスターへの道 ビュー(VIEW)
Mick’s Page ビューの功罪

-Database
-

執筆者:


comment

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

関連記事

no image

集合としてのSQL

今まで何度かSQLが集合ということを扱ってきました。 今回はそれがよくわかる例を。 実務ではあまりないと思うのですが、集合をイメージしやすい例題として、2つのテーブルが全く同じケースを考えます。 下記 …

no image

slow-query-logについて

データベースを伴う部分でののチューニングですが、大きく分けると SQLを書き直す インデックスを張りなおす プログラム内部でキャッシュを有効化する 設定ファイルの修正 上記のようなかんじになるのではな …

no image

アンチパターン バインド変数の未使用+直積組み合わせ+データ量爆発+インデックス関連

本日はSQLコーディングに関して。 ここら辺は実際にプログラムを書く際に重要になってくるネタ。 Contents1 バインド変数1.1 デメリット1.2 対策2 直積により組み合わせが爆発する2.1 …

no image

checkboxでの値の管理

formにてcheckboxの値を一つのカラムにいれて管理する機会があったのですが、これ入力更新出会っても検索であっても処理がなかなか厄介です。特に検索の時ですね・・・ 要するに値の候補が1,2,3, …

no image

アンチパターン トランザクションスコープ+大量データのリアルタイム集計+接続が詰まる

本日は主にインフラの設計的なことに関して。 Contents1 トランザクションスコープの設定1.1 デメリット1.2 対策2 大量データのリアルタイム集計2.1 デメリット2.2 対策3 詰まると接 …