Help us understand the problem. What is going on with this article?

【社内勉強会】DB設計の基礎(2017/06/21)

More than 3 years have passed since last update.

【社内勉強会】DB設計の基礎(2017/06/21)

by yuji38kwmt
1 / 24

0.はじめに


対象者

  • 初めてDB設計する人

前提知識

  • 基本的なSQL
  • PostgreSQL
  • 応用情報技術者レベルの知識(正規化)

伝えたいこと


参考図書

達人に学ぶDB設計 徹底指南書
DB設計_指南書.png

SQLアンチパターン
アンチパターン.gif


目次

  1. 設計の概要
  2. 論理設計
  3. 物理設計

1.設計の概要


用語

  • データベース:データの集積
  • DBMS(Database Management System):データベース

代表的なデータベースのモデル

  • リレーショナルデータベース(RDB)
  • キー・バリュー型ストア

データベースシステムとファイルシステム

http://ossforum.jp/node/708

  • データに制約を付けられる
  • データ一貫性保持(トランザクション管理)

そのデータ、本当にDBに格納する必要ありますか?
http://www.oracle.com/technetwork/jp/articles/index-155208-ja.html


データ中心アプローチ(Data Oriented Approach: DOA)

最初にデータがある。プログラムはその次にできる。

昔はプロセス中心アプローチだった。


DBの論理設計と物理設計

論理設計、物理設計の順に行う。

~論理設計で行うことは何かですが、それは現実世界に存在する数多くのデータから、リレーショナルデータベースにおいて、何を、どのようなフォーマットで保存するかを決めることです。

「達人に学ぶ DB設計指南書」P30 より引用。

物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程です。

「達人に学ぶ DB設計指南書」P34 より引用。


論理設計のステップ

  1. エンティティの抽出
  2. エンティティの定義
  3. 正規化
  4. ER図の作成

エンティティは、現実世界に存在するデータの集合体を指す言葉。
たとえば、「顧客」、「社員」、「注文」など。


物理設計のステップ

  1. テーブル定義
  2. インデックス定義
  3. ハードウェアのサイジング
  4. ストレージの冗長構成決定
  5. ファイルの物理配置決定
  • [感想] インデックス定義をしていない…

2. 論理設計


正規化の目的

  • 人間が理解できる形で、現実世界の事実を表現する
  • 事実の格納方法から冗長性を排除し、データの異常や不整合を防ぐ
  • 整合性制約をサポートする

「SQLアンチパターン」P292 より引用

  • 原則として、第3正規形までは正規化を行う

※正規化の具体的な手順は、省略します。


自然キーと代理キー

  • 自然キー(natural key): 入力データに最初から存在しているキー
  • 代理キー(surrogate key): 自然キーの代理。システム側で決めた連番などの、人工的なキー

主キーが決められない、または不十分なケース(自然キーがない)

  • 一意なキーがない
  • 一意なキーだが、サイクリックに使いまわされている
  • 一意なキーだが、途中で指す対象が変わる

「DB設計指南書」P226 参照

以下、参考サイト
https://amg-solution.jp/blog/8980
http://wa3.i-3-i.info/simple/word1995.html


オートナンバリングの是非

ネット上で議論されている。

オートナンバーを主キーに使うことは、データモデルを書いている証拠だ。

by ジョー・セルコ

オートナンバリングの実装方法

  • データベースのシーケンスオブジェクト、ID列
    • PostgreSQLならば、sequence, serial型
    • PostgreSQLのserial型はDEFAULT句にnextval(sequence)を設定している

アプリケーション側での実装はダメ!!

  • コストがかかる
  • 排他制御の仕組みが必要。二人以上が同じ番号を引き当てる可能性あり。

アンチパターン EAV(Entiti Attribute Value)

可変属性をサポートする設計。

  • SELECT文が冗長で、分かりにくくなる
  • 参照制約、NOT NULL制約が付けられない ⇒ 整合性がとれない

物理設計


[個人的見解] 制約をできるだけつける

制約の種類

  • 主キー制約
  • 外部キー制約
  • 一意性制約
  • CHECK制約
  • NOT NULL制約

制約をつける目的

  • 格納されているデータの内容を保証

制約を付けないと、アプリケーションのソースコードを調べる必要がある

  • 調べ方に漏れはないか?
  • アプリケーションを使わず、直接DBに操作している場合はないか?

[経験] 制約をつけていれば見つけられたバグだったのに…


付録


リレーショナルデータモデルと相性が悪いデータ

  • 履歴データ

リレーションは集合です。よって、各要素同士に順序はありません。しかし、履歴にはどちらかが古いのか、新しいのかという順序が存在します。

「データベース実践入門」P182より引用。

  • グラフ理論のグラフ(ツリーもグラフの一種)

論理削除の是非

DELETE_FLAG という思考停止フラグ

「その論理削除した行は、どういう時に参照しますか?」
「論理削除した行が復活する事がありますか?」
「論理削除した行は最終的にどうなりますか?」

http://qiita.com/Jxck_/items/156d0a231c6968f2a474 より引用

  • [感想] 私は全テーブルの削除フラグを付けてしまっています…

そのうち話したいこと

  • SQLアンチパターン
    • 単一参照テーブル
    • IDリクワイアド(とりあえずID)
  • SQLの速度改善
  • 関係データモデル
yuji38kwmt
愛知のIT企業で修行しております。2018年4月に転職しました。 基本的に自分用のメモとして、記事を書いております。 所属先の見解とは一切関係ありません。 https://qiita.com/yuji38kwmt/items/a474ad97e0d86f6081a2
kurusugawa
「いいソフトウェアを楽に作る」技術を追求する企業。今は、機械学習、画像認識中心。
http://kurusugawa.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away