SSSSLIDE

楽しいデータベース設計の講義
Yohei Munesada

宗定洋平 (むねさだようへい)
http://www.yoheim.net
Python, Node.JS, Java, Go, Swift, PHP, Frontend, etc
2

この講義のゴール
卒業制作のデータベース設計ができるようになること
3

データベース設計の全体像と今回の範囲
論理設計
物理設計
データの論理的な構造を
データベースの性能を
明らかにする
最大化する
4

論理設計
物理設計
データの理解
データ容量の見積もり
エンティティの定義
パフォーマンスの設計
リレーションシップの定義
障害対応の設計
データ項目の定義
監視の設計
列の定義
5

論理設計
物理設計
データの理解
データ容量の見積もり
パフォーマンスの設計
障害対応の設計
監視の設計
列の定義
6

目次
a.m.
p.m.
✓ データベース設計の全体像
✓ 個人演習とチーム演習
✓ サンプルアプリの紹介
✓ 論理設計
✓ データの理解
✓ 良いデータベース設計とは
✓ エンティティの定義
✓ リレーションシップの定義
✓ データ項目の定義
✓ 列の定義
✓ (参考) 物理設計
7

チーム分け
テーブルで１チームになってください
8

Database Design

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
10

モックアプリをみてみよう
11

モックアプリをみてみよう
12

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
13

データの理解
演習
アプリの中にどのようなデータが存在するのか？
各データのライフサイクルは何か？
14

データベース設計について考えよう
演習
良いデータベース設計とは何か？
15

良いデータベース設計とは何か？
参照と更新が行いやすいこと
画面を表示する際に、Select文を容易に書けるか
画面を表示する際に、Select文のパフォーマンスは良いか
データを更新する際に、矛盾なく追加/更新/削除ができるか
将来の変更に強いこと
機能を追加する際に、簡単に追加することはできるか
16

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
17

エンティティとは物事を概念化してモデルとして表現したものです。
商品
エンティティ名
商品コード
商品名
属性名
単価
18

エンティティを具象化したものをインスタンスと言います。
商品
001
テレビ
¥48,000
商品コード
商品名
単価
002
PC
¥64,000
エンティティ
インスタンス
19

基本キー
エンティティを一意に識別するためのキーを基本キー(=PK)と言います。
商品
001
テレビ
¥48,000
商品コード
商品名
単価
002
PC
¥64,000
エンティティ
インスタンス
20

演習
モックアプリのエンティティを洗い出そう。
21

演習
モックアプリのエンティティを洗い出そう。
投稿
いいね
ユーザー
コメント
22

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
23

リレーションシップとはエンティティ間の関係を表現ものです。
商品
注文詳細
注文
商品コード
注文コード
注文コード
商品名
商品コード
注文日
単価
数量
24

多重度
エンティティ間の関係には、多重度(=カーディナリティ)が存在します。
1対1
エンティティ
エンティティ
1対多
エンティティ
エンティティ
多対多
エンティティ
エンティティ
25

オプショナリティ
多重度では、ゼロを含むか否か(=オプショナリティ)も考慮します。
必須である
エンティティ
エンティティ
エンティティ
エンティティ
必須でない
エンティティ
エンティティ
エンティティ
エンティティ
26

オプショナリティ
オプショナリティは、「1..n」のように表現する場合もあります。
1
必須である
エンティティ
エンティティ
1..n
エンティティ
エンティティ
0..1
必須でない
エンティティ
エンティティ
0..n
エンティティ
エンティティ
27

1対1の例
見積もりを作ってから購入を行う場合、見積もりと購入は1対1の関係となる。
見積もり
購入
見積もり1
見積もり2
購入1
見積もり3
購入2
28

1対多の例
部署には複数人の社員が属するため、1対多の関係となります。
（※ ここでは兼務は考慮しません）
部署
社員
旧監査室
Aさん
開発部
Bさん
社長室
Cさん
29

多対多の例
商品と注文は多対多の関係となります。
商品
注文
扇風機
注文1
冷蔵庫
注文2
スマホ
注文3
パソコン
注文4
30

多対多の関係は避けるべし
多対多の関係はMySQLなどで表現できないため、1対多に変換します。
商品
注文明細
注文
扇風機
注文1の明細1
注文1
注文2の明細1
冷蔵庫
注文2
注文3の明細1
スマホ
注文3
注文3の明細2
パソコン
注文4
注文4の明細1
31

演習
モックアプリのエンティティ間のリレーションシップを定義しよう。
(作成イメージ)
大分類
柄
デザイン
素材
中分類
商品
カラー
SKU
サイズ
32

演習
モックアプリのエンティティ間のリレーションシップを定義しよう。
いいね
ユーザー
投稿
コメント
33

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
34

各エンティティの持つデータ項目（属性）を定義します。
商品
アプリに含まれるデータを漏れなく盛り込みます
→ 漏れを見つけたらその時点でデータ設計を見直します
商品コード
商品名
何をどのエンティティに入れるべきなのか？
単価
→ 正規化を学びましょう
35

データの正規化のイメージ
Before
After
36

データの正規化とは
正規化の目的
データの追加/更新時に壊れないようにする（整合性の担保）
正規化の種類
「第1正規化」「第2正規化」「第3正規化」の3種類
37

非正規化の状態
正規化前の状態では、データの中に繰り返し項目が存在します。
とあるお店の注文データ
38

正規化前の状態
非正規化の状態では、データの中に繰り返し項目が存在します。
とあるお店の注文データ
演習
このデータ構造で運用すると色々と問題があります。
何でしょうか？
39

第1正規化
非正規化の状態から繰り返し項目をなくすと、第1正規化となります。
40

基本キーの定義
非正規化の状態から繰り返し項目をなくすと、第1正規化となります。
演習
このデータの基本キーは何でしょうか？
41

基本キーの定義
基本キー（主キー）をベースに、第2正規化以降を行います。
基本キー1
基本キー2
42

第2正規化
第2正規化では、部分関数従属を排除します。
部分関数従属とは？
あるデータが基本キーの一部に従属している状態です。
テーブルを分割します
部分的関数従属
43

第2正規化
第2正規化では、部分関数従属を排除します。
演習
全ての部分的関数従属を見つけましょう。
44

第2正規化
伝票番号のみに従属するデータと、商品コードにのみ従属するデータが存在します。
45

第2正規化
部分的関数従属のデータは、エンティティを別に作成します。
注文明細
商品
注文
46

第3正規化
第3正規化では、推移的関数従属を排除します。
注文
推移的関数従属とは？
基本キー以外のデータ同士で従属関係がある状態です。
テーブルを分割します
推移的関数従属
47

第3正規化
第3正規化では、推移的関数従属を排除します。
注文
演習
全ての推移的関数従属を見つけましょう。
48

第3正規化
店舗IDと担当者IDにそれぞれ推移的関数従属のデータが存在します。
49

第3正規化
推移的関数従属のデータは、別エンティティとして定義します。
注文
店舗
担当者
50

正規化まとめ
エンティティの分割結果の Before / After です。
Before
51

正規化まとめ
エンティティの分割結果の Before / After です。
After
売上
売上明細
店舗
商品
担当者
52

(参考1) 導出項目の削除
算出できる数値は、エンティティの属性に定義しないことが基本です。
売上
× × ×
売上明細
店舗
商品
×
担当者
53

(参考1) 導出項目の削除
しかし、敢えて導出項目を残す/作成する場合もあります。
売上金額やtaxなど残しておく必要がある場合
例：その時に割引して販売した
例：tax率は年月によって異なる
頻繁に導出項目にアクセスしたい場合
例：ユーザーのLike総数を表示
OrderByやGroupByで使いたい場合
例：売上順にソート
54

(参考2) 正規化を崩す場合
第3正規化が基本ですが、敢えて正規化など崩す場合があります。
演習
どのような場合に第3正規化を崩すでしょうか？
55

(参考2) 正規化を崩す場合
第3正規化が基本ですが、敢えて第2正規化など崩す場合があります。
答え
パフォーマンス向上を目的とするとき
販売などその時点のデータを残す必要があるとき
56

演習
エンティティのデータ項目を定義し、
第3正規化を満たすかをチェックしてみよう。
57

演習
エンティティのデータ項目を定義し、
第3正規化を満たすかをチェックしてみよう。
ユーザー
いいね
コメント
投稿
ID
ユーザーID
ユーザーID
ID
ログインID
投稿ID
投稿ID
タイトル
ユーザー名
コメント
説明文
パスワード
ユーザーID
誕生日
値段
画像URL
58

目次
a.m.
p.m.
✓ 論理設計
✓ 列の定義
59

列の定義
列の定義では、3つのことを行います。
列名の定義（論理名 / 物理名）
プライマリーキー、ユニークキー、外部キーの定義
列のデータ型の定義
60

列の定義
61

列名の定義 (論理名 / 物理名)
エンティティと属性の論理名と物理名を決めていきます。
商品
Products
id
name
price
担当者
Staffs
id
name
62

エンティティと属性の論理名と物理名を決めていきます。
Sales
id
売上
shop_id
sale_date
total
tax
total_and_tax
staff_id
売上明細
Sale_details
sale_id
product_id
amount
total
63

物理名をつけるときのTipsです。
テーブル名は単数形か？複数形か？
どっちでも良い。ただしライブラリによってオススメがある。
列の物理名をつけるのが難しいんだけど？
辞書を引きながら頑張る。
ただし、業務特有名称はローマ字でつけることもある。
他のテーブルを参照する列名の付け方は？
「（参照先の）テーブル名_列名」が基本。
僕の場合には「fk_テーブル名_列名」にすることもある。
64

列の定義
65

各エンティティについて、PK、ユニークキー、外部キーを定義します。
プライマリーキーとは
1レコードを一意に特定するためのキー
定義は「ユニークキー + NOT NULL」
パフォーマンス観点から各テーブルにプライマリキーをつける
ユニークキーとは
1つのエンティティの全レコードの中で、一意になる値
NULLも入れることも可能
時々使う場面がある
外部キーとは
エンティティ間のリレーションを表現するキー制約
使わないので割愛します（プロジェクト方針による）
エンティティ間の整合性はプログラムで保証する
66

列の定義
67

データ型の定義
各データ項目について、格納するデータに合わせた型を定義します。
また、格納するデータ量に合わせた桁数/精度も定義します。
数値型
INT / DECIMAL / DOUBLE
小数点を含む金額(ドルやビットコイン)の場合には、Decimalを使う
日付型
DATE / DATETIME
文字列型
CHAR / VARCHAR / TEXT / BLOB
https://dev.mysql.com/doc/refman/5.6/ja/data-type-overview.html
68

論理設計の振り返り
a.m.
p.m.
✓ 論理設計
✓ 列の定義
69

(実験1) 変更に強い？
ユーザーフォローの仕組みを入れるとしたら？
いいね
ユーザーID
ユーザー
投稿
投稿ID
ID
ID
ログインID
タイトル
ユーザー名
説明文
コメント
パスワード
ユーザーID
誕生日
ユーザーID
値段
投稿ID
画像URL
コメント
70

ユーザーフォローの仕組みを入れるとしたら？
フォロー
ユーザーID
フォローID
いいね
ユーザーID
ユーザー
投稿
投稿ID
ID
ID
ログインID
タイトル
ユーザー名
説明文
コメント
パスワード
ユーザーID
誕生日
ユーザーID
値段
投稿ID
画像URL
コメント
71

投稿の下書きの仕組みを入れるとしたら？
いいね
ユーザーID
ユーザー
投稿
投稿ID
ID
ID
ログインID
タイトル
ユーザー名
説明文
コメント
パスワード
ユーザーID
誕生日
ユーザーID
値段
投稿ID
画像URL
コメント
72

投稿の下書きの仕組みを入れるとしたら？
いいね
投稿
ユーザーID
ユーザー
投稿ID
ID
ID
タイトル
ログインID
説明文
ユーザー名
ユーザーID
コメント
パスワード
値段
誕生日
ユーザーID
画像URL
投稿ID
ステータス
コメント
73

投稿の編集履歴の仕組みを入れるとしたら？
いいね
ユーザーID
ユーザー
投稿
投稿ID
ID
ID
ログインID
タイトル
ユーザー名
説明文
コメント
パスワード
ユーザーID
誕生日
ユーザーID
値段
投稿ID
画像URL
コメント
74

投稿履歴
投稿の編集履歴の仕組みを入れるとしたら？
投稿ID
revID
タイトル
説明文
値段
画像URL
いいね
ユーザーID
ユーザー
投稿
投稿ID
ID
ID
ログインID
タイトル
ユーザー名
説明文
コメント
パスワード
ユーザーID
誕生日
ユーザーID
値段
投稿ID
画像URL
コメント
75

Physical design

物理設計
物理設計の目的は2つあります。
求められるパフォーマンスを提供すること
安定した運用ができること
77

物理設計
目的に対して手段は以下の通りです。
性能設計
安定運用
ディスク容量の見積もり
障害対応設計
非正規化や導出項目の追加
監視設計
インデックスの作成
メンテナンス設計
SQLのチューニング
トランザクションの分離レベルの定義
パーティション化やディスク分散
78

ディスク容量の見積もり
ディスク使用量は「1行あたりのバイト数 × 列数」で計算します。
投稿テーブル
① 1行あたりのバイト数
約600バイト/行
③ 必要バイト数
② 増加量
6MB/日
約10,000件/日
180MB/ヶ月
2.16GB/年
※ 増加が見込まれる全テーブルで行います。
79 ※https://dev.mysql.com/doc/refman/5.6/ja/storage-requirements.html

午前中の復習
a.m.
p.m.
✓ 論理設計
✓ 列の定義
80

Break time

演習
卒業制作についてデータベース設計を行ってみましょう。
✓ 個人ワーク 20分
✓ リレーションの定義
※ 積極的に助け合ってください
✓ グループワーク 20分
✓ チーム内で発表＆フィードバック
✓ 全体発表（1∼2名） 20分
✓ 発表+質疑応答
82

Summary

まとめ
a.m.
p.m.
✓ 論理設計
✓ 列の定義
84

さらに学びたい人は
https://www.jitec.ipa.go.jp/1_11seido/db.html
85

Thank you
@yoheiMune

SSSSLIDE

G'sデータベース設計の講義

Yohei Munesada さんの他のスライド

関連するスライド

このスライドの内容

関連するタグ

SSSSLIDE