Zenn
Closed10

はじめてのDB設計

db設計
プラハチャレンジ
kzk4043kzk4043

DB設計/ER図書き方の流れ

https://www.ntt.com/business/sdpf/knowledge/archive_50.html#:~:text=ER図とは、「データベース,的に設計します。
https://products.sint.co.jp/ober/blog/create-er-diagram

サイトによって違うけどこんな感じぽい

5.1. システムシナリオを確認する
5.2. エンティティを洗い出す
5.3. エンティティをマスタ系とトランザクション系に分ける
5.4. アトリビュート（属性）を洗い出す
5.5. ER図に落とし込む

現実から徐々にDBに変換していく感じ？

kzk4043kzk4043

3層スキーマ

DBを下記3つに分けて理解するというか設計するための考え方？分類の仕方？DMBOKというのが元ネタ？

  • 外部スキーマ
  • 概念スキーマ
  • 内部スキーマ

外部スキーマ

ユーザからみたデータベース。ビューの世界（ビューってなに）。API化した場合のIF的なものかな？

概念スキーマ

開発者からみたデータベース。IFはこっちか？概念スキーマの設計を論理設計ともいう。概念スキーマはデータの独立性のためにあると出典１-P20にあるが、イマイチわからん。

論理設計

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

内部スキーマ

DBMSからみたデータベース。内部スキーマの設計を物理設計という。

物理設計

  1. テーブル定義
  2. インデックス定義
  3. ハードウェアのサイジング
  4. ストレージの冗長構成決定
  5. ファイルの物理配置決定
kzk4043kzk4043

正規化

テーブル分割によって正規化していく。

第1正規形

1つのセルには1つの値しか含まない

↑主キーが各列の値を一意に特定できないから

第2正規形

完全関数従属のみのテーブルを作る。＝異なるレベルの実体をきちんとテーブルとして分離してやる

  • 部分関数従属だと、主キーの一部が不明な場合などにレコードを登録できない
  • 部分関数従属の場合に間違ったレコードが追加される可能性がある

第3正規形

推移的関数従属の解消。

↑ここまでが実用的なレベルの正規形らしい

↓ここからはかなり厳密さが必要な場合に考慮すべき、くらい？

第3.5正規形＝ボイスーコッド正規形

いまいちよくわからんな

第4正規形

関連エンティティってのは中間テーブルのことか…？その中での関連は一つだけにする

第5正規形

んーなんかなんとなくはわかるけどちゃんとわかった気はしないな…実際に業務でもこうやって順を追って分解しているものなのか、それともとりあえず分解してくような感じなのか。
従属性が大事そうだが、その定義が一番難しいんじゃないだろうか。結局業務ロジック次第。

正規化のメリット

  • データの冗長性排除、更新時の不整合防止
  • テーブルの持つ意味が明確になる

正規化のデメリット

  • テーブル数が増え、結合を多用するのでパフォーマンスが下がる

正規化の功罪

正規化はデータの整合性を保つためにするものだが、その分パフォーマンスを犠牲にするらしい。結局テーブルを分割するので、SQLにおいて結合が必要となるため（結合はSQLの処理の中でも高コストとのこと）。正規化によるデータ整合性とパフォーマンスはトレードオフで、どの程度正規化するかは人によって主張が異なるらしい。

非正規化

パフォーマンスが重視される場合は、非正規化が一つの選択肢になるが、もちろんデータ整合性をある程度犠牲にすることになる。あとはデータの書き込み時にも不利。出典１の筆者によると、（個人の意見と前置きした上で）非正規化は最後の手段で、基本的には正規化度が高いほど良い設計。他の手段でパフォーマンス向上を目指すべきで、ほんとにどうしようもないときにだけ非正規化する、とのこと。

kzk4043kzk4043

ER図

いくつか書き方があるが、代表的なのはIE表記法（鳥の足記法）、IDEF1X。とりあえずIEでいい気がする。

  • リレーション

    • 1対1
      • あまりない。1対1ならまとめてしまえばいい
    • 1対多
      • テーブル間の関係は基本的に１対多
    • 多対多
      • 多対多の関係は１対多に分解。その際に必要になるエンティティを「関連実体」とよぶ

  • テーブル間の関係は基本的に１対多。多対多の関係は１対多に分解。その際に必要になるエンティティを「関連実体」とよぶ

kzk4043kzk4043

久々に見返すとテーブル名にTとかMとか接頭辞がついている（M_USERSとか）が…マスタとトランザクション…？

kzk4043kzk4043

パフォーマンス

インデックス

これを使わないシステムはないくらいのやつらしい。名前からも想像しやすい仕組み。

  • アプリケーションコードに影響を与えない（アプリケーション透過的）
  • テーブルのデータに影響を与えない（データ透過的）
  • それでいて性能改善の効果が大きい

これだけみるとたしかに使わない手はないって感じか。

色んな種類があるが、まずはB-treeインデックスがよく使われるので重要とのこと（Bの出どころは不明らしいｗ）。バランス型で汎用性が高いインデックス。

インデックスが利用できていないDMLがいくつか出ているが、利用できていないかどうかってどうやったらわかるんやろ…速度？

統計情報

どうやってデータを取りに行くかというHowにあたる部分？DBMSに任せるケースが多いということだが、これをチューニングすることができるってことか？

んんん…統計情報の実体がいまいちわからない…ルートっていうのの実体もわからないし。たぶん触らないとわからないから一旦置いとく。

kzk4043kzk4043

実際にやってみてよくわからなかったことメモ

  • PKは複数の値の組み合わせでいける？
    • 出典1を見る限りいけそう
  • ID名は例えばcustomer_idみたいにDB内で固有の名前をつける？FKになるときだけ固有の名前にするとかだと、あとでFKになったときに困る？
  • 例えば全体割引クーポンと商品ごとの割引クーポンがあるとして、どうテーブル設計する…？
    • 全体割引は注文に紐づけて、商品割引は別途テーブルを作って注文詳細に紐付け？両方適用のケースが複雑？
    • クーポン側に対象となる範囲を登録して、注文に紐付ける？
  • 例えばすしとすしを何種類かまとめたセット商品がある場合、注文詳細にはどう紐付ける？
    • 注文詳細にすし/セットのIDどちらかが入るようにする？
    • 商品テーブルを作ってIDを振り直す？でも上と本質は一緒では？
  • 例えばすしとセットを別テーブルにして、IDをUUIDとかにしといたら、例えば注文詳細で一つのitem_idとして使えるのか？
  • マスタとトランザクションは分ける必要があるのか？なんのために分けている？
  • どちらか一方の値を取るようなケースはどういう実装がいいのか。例えば、なにかの商品の割引クーポンのテーブルがあるとして、率による割引と額による割引があるとしたとき。
    • 2つのプロパティを設けてどちらかだけに値をいれる→両方に入る危険性はない？
    • テーブルを分ける→参照先のテーブルでIDの欄が結局2ついる？
kzk4043kzk4043

その他

アンチパターン

https://www.oreilly.co.jp/books/9784873115894/

  • カンマ区切りで複数の値を格納
  • 複数値についてvalue1,value2,value3のカラムを用意
  • 「漫画と小説どちらにもコメントを紐付けられるようにしたい」という要望を受けて、前任のエンジニアは「belongs_to_id」を定義し、そこにmanga_idあるいはnovel_idを格納することで解決しました。また、belongs_to_idがmangaとnovelどちらを指しているのか判別できるよう「type」も追加しました
  • ツリー構造をリレーショナルデータベースで表現する際（例えばslackのようなスレッドを表現する時など）に、親の ID だけを持つツリー構造
  • Salesforceのようなサービスを想像してみてください。以下のようなテーブルで新規顧客の営業進捗を管理
TABLE NewCustomer {
  id: varchar
  called: boolean -- 電話をかけたらTRUEになる。FALSEの人には電話をかけなければいけない
  callNote: varchar -- 電話をかけた時に交わした内容のメモ
  metOnce: boolean -- アポで面談したらTRUEになる
  metAt: date -- 面談をした日付が入る
  closed: boolean -- 成約した
  closedAt: boolean -- 成約した日付が入る
}
  • 生徒を管理するテーブルで、「在学中」「卒業」「停学中」などのステータスが存在します。statusカラムには「studying」「graduated」「suspended」いずれかの値しか入らないようにcheck機能を使っています。MySQLやPostgreSQLでは上記のようにCheck制約を設けることで挿入できるデータを限定できます
  • とりあえず削除フラグ
  • 企業Aの担当者に話を聞いたところは現在企業Aが用意している商品カタログにつけられている商品ごと「商品コードを見れば商品を一意に特定できる」とのことでしたので、そのまま商品コードを主キーに使ってみました
  • MySQLやPostgreSQLにåる「Check」「Enum」「Domain」「Trigger」など、システムの仕様をデータベースに強制するために様々な機能

マルチテナント

  1. テナント毎にデータベース/スキーマを分割する
  2. テナント毎にテーブルを分割する
  3. テーブルにテナント識別カラムを持ち、行単位で制御

直感的にはテーブル分割あたりが多いのかと思っていたけど、RLSを使った行単位制御が多そうな印象だった。

外部キー制約

とりあえずつけときゃいいくらいに思っていたけど、それなりに結構議論の余地がある様子。ちょっと理解できない議論もいくつかあったので、「外部キー制約　メリット　デメリット」あたりで調べてみる。

Null

「そもそもNULLが存在する事自体がリレーショナルデータベースにおいてあり得ない事」と唱える人も居るぐらい、基本的にNULLは避けたいもの

このスクラップは2023/12/15にクローズされました