LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Kkeisuke-shiki(k k)

DB設計

Posted at

導入

要約

この記事では、データベース設計の基本原則やキーの種類、制約の役割について解説しています。特に、カラムは細かく分割し、親子関係を正しく管理することが重要とされています。主キーはナチュラルキーかサロゲートキーを目的に応じて選び、制約でデータの整合性を保ちます。また、テーブル名やカラム名の命名規則も説明され、リレーションの数学的背景にも触れています。

核心

カラムはなるべく細かい単位にしろ

  • 意味的に分割できるなら、分割する
  • 例：名前(name) → 姓(last_name) + 名(first_name)
  • 理由
    • 「山田太郎」「山田 太郎」など、表記の差異が一意性を壊す恐れがある
    • 「阿倍仲麻呂」や「源義経」など姓名の区切りがわかりにくい人がいる
    • 並べ替え・検索・外部連携が柔軟になる

孤児を生むな

  • カスケードを行う。
    • 子供(外部キー)→親(主キー)の順に削除する
    • 子供(外部キー)→親(主キー)の順に変更する

主キーを可変長文字列にする場合は、固定長文字列＋表記体系を厳格に定めろ

  • 表記ゆれや同じ意味でも表記が異なる場合はあると、主キーの役割がなくなる

主キーはナチュラルかサロゲートか、目的に応じて選べ

  • ナチュラルキー：ユーザーに意味がある（例：社員番号, 学籍番号）
  • サロゲートキー：システム的に安定（例：自動採番ID、UUID）
  • どちらを使うかはプロジェクトの要件・規模次第。

列の意味を変更するな

  • 体重のカラムに年齢や身長のデータを入れるな。
  • 当たり前過ぎてこれ以上は書かない。

同じテーブルを量産すな。１つだけにしろ

  • 同じ役割のテーブルは１つでいい。そのまま。
  • table_aとtable_bで同じ場合は、データ精査(データクレンジング)を行い、1つのテーブルにする。
  • システム統合で発生する可能性がある。

本文

テーブル

テーブルの構成

  • 実務では、「属性」とは言わない。レコードや列と言った方が伝わる。

テーブルの種類

マスタテーブル

  • システム利用開始時の段階から入っていないと困るデータをもつテーブルのこと。
  • 単に「マスタ」と呼ばれることが多い。

トランザクションテーブル

  • システムを利用していく中で、蓄積されていくデータをもつテーブルのこと。

キー

スーパーキー ⊃ 候補キー ⊃ 主キー  (1つ選ばれる)
                    　 ⊃ 複合キー(複合選ばれる)

[スーパーキー]
 ├─ 候補キー
 │   ├─ 主キー（1つ選ばれる）
 │   │   └─ 複合キーのこともある（複数列）
 │   └─ その他の候補キー（ユニークな列）
 └─ 超過列を含むキー

スーパーキー

  • レコードを一意に識別できる任意の列の組み合わせ。
  • 候補キーよりも広い概念。つまり、候補キー＋不要な列も含む。
  • あまり業務では重要な役割を果たさないから、そういうものがあるのか程度に
  • 例：社員番号（候補キー）や 社員番号 + 氏名（スーパーキー）

候補キー

  • 主キーになり得る列（または列の組）。
  • テーブル内でレコードを一意に識別できるすべてのキーの候補。
  • 主キーはこの中から1つ選ばれたもの。
  • あまり業務では重要な役割を果たさないから、そういうものがあるのか程度に
  • 例：社員テーブルで「社員番号」　「メールアドレス」の両方が一意なら、どちらも候補キー。

主キー(primary key)

  • テーブルにおいて必ず1つのみ存在し、各レコードを一意に識別（unique）できる列。

  • NULLは不可、重複も不可。
    -　 自動的に NOT NULL ＋ UNIQUE を含む

  • 例：社員番号、ID、マイナンバーなど。

  • 主キーには、ナチュラルキーとサロゲートキーの２種類ある。

    • ナチュラルキー（Natural Key）

      • 実世界で意味のある情報を使ったキー
      • 例：社員番号、メールアドレス、ISBN、国コードなど
      • ユーザーにとっても意味がある（覚えやすい）

    • サロゲートキー（Surrogate Key）

      • システムが内部的に割り当てるID
      • 例：自動採番の整数ID（id）、UUIDなど
      • ユーザーにとっては意味がない
    概念 用語 特徴
    意味のある主キー ナチュラルキー 実世界に意味があり、ユーザーに優しい
    意味のない主キー サロゲートキー システムが生成、安定性と柔軟性が高い

複合キー

  • 複数の列を組み合わせて一意に識別する主キーのこと。
  • つまり、そのテーブルには、その組み合わせは他に存在しないということ
  • 例：注文ID + 商品ID → 注文詳細テーブルでの主キー。

外部キー

  • 他のテーブルの主キーを参照するキー。
  • リレーション（関連）を作るために使用され、参照整合性を保つ(参照整合性制約)。
  • 設定した外部キーが主キーを参照する性質のことで、必ず主キー側は存在している。
  • 例：ordersテーブルのcustomer_idが、customersテーブルのidを参照。
    • 外部キー(複数) → 主キー(一つ)
  • 参照される主キーを親とすると、参照する外部キーを子供と見ることができる。
    • 主キーと外部キーは親子関係と同じ
    • 独身（＝子供を持たない親）は存在しても問題ない。
    • 親が存在しないのに子供が存在することは、参照整合性違反となる。
    • つまり、親子関係があるのに「親が削除されたのに子供が残っている」といった状態は許されない。
    • この場合は、親子ともに削除か、子供をNULLにして親を削除する。
    • 親が削除されたときにその子供を削除する動作をカスケードという。
    • 親を変更したときも同様である。

制約

  • 制約とは：テーブルに保存されるデータに対して「守るべきルール」を設定するもの。
  • 不正なデータを事前にブロックすることで、データの一貫性や正確性を守る。

NOT NULL制約（ヌル禁止）

  • 列に NULLを許さない（必ず値が入っている必要がある）
  • よく主キーや必須入力項目で使われる

一意制約（UNIQUE）

  • 重複を禁止する制約
  • 主キーと違い、NULLは許容される場合がある
  • 例：メールアドレスや電話番号など

CHECK制約

  • 値の範囲や条件を指定できる
  • 条件を満たさないデータの登録を防ぐ
  • 例：年齢(0～120歳)

命名

  • テーブル名・カラム名には以下の文字のみを使用すること

    • 半角アルファベット（a～z, A～Z）
    • 半角数字（0～9）
    • アンダースコア（_）

  • 名前の先頭は必ずアルファベットにする。

  • 重複する名前を付けない

    • 同一テーブル内で同名のカラムは定義できない。
    • 同一スキーマ内で同名のテーブルは定義できない。

  • テーブル名は原則、複数形または集合名詞で命名する

    • 例：users, orders, products
    • ただし、実際の現場では「複数形にしない」　「英語の文法的な複数形ではない」ケースも多く、組織ごとの命名規則に従うことが最も重要である。

【おふざけ】リレーション

  • ここ、数学ガチ勢が本気出すゾーンです。
  • 調べてみたら、見事に心が折れました。ここはもう大学数学（集合論など）で地獄。
  • なので今回は、雰囲気だけつかむ作戦でいきます。

リレーションは、「n項関係（n-ary relation）」

  • リレーションは、数学における「n項関係」ってやつが元ネタ。
  • 結論：「テーブル」と「表」は似て非なるモノ！
    • リレーションとは、「1つのテーブル（構造とデータ）を数学的に定義したもの」であって、「テーブル同士の関係」じゃない。
    • 重複レコードはない。
    • レコードに順番はない。
    • カラムの並び順も意味なし。

ちょっとだけ集合論っぽい前提知識

  • 集合とは？

    • 同じ要素を何回入れても「1個」になる！（例：{(1, 2), (1, 2)} = {(1, 2)}）
    • 順番は関係ない！集合は「順序不同」。順番が変わっても同じ集合。

  • 直積ってなに？

    • 複数の集合から要素を1つずつ取り出して、全部の組み合わせを作ったもの！
    • 例：A = {1, 2}, B = {a, b}の直積(A × B)
      • A × B = { (1, a), (1, b), (2, a), (2, b) }

  • リレーションとは？

    • 上の「直積」から、実際にあるデータだけを選んだ部分集合。

  • 部分集合(⊆)とは、2つの集合A，Bがあって，AがBの一部分であるという状態のこと。

    • つまり、集合AとBの間には包含関係があるということ。

リレーションの定義（ちょっとだけ数学風）

リレーション R(A₁, A₂, ..., Aₙ) は以下のように定義される：

R(A₁, A₂, ..., Aₙ) ⊆ dom(A₁) × dom(A₂) × ... × dom(Aₙ)
  • R：テーブルの名前（社員、商品など）
  • A₁〜Aₙ：属性（カラム）の名前（社員番号、名前など）
  • dom(Aᵢ)：各属性が取りうる値（ドメイン）

実際に考えてみよう！

  1. 道具をそろえる

    • リレーション名 R

      • テーブルのようなものの「名前」です（例：社員、顧客など）

    • 属性名 A₁, A₂, ..., Aₙ

      • テーブルの「列名」みたいなものです（例：社員番号、名前、年齢 など）

    • ドメイン関数 dom

      • 各属性が取りうる値の「集合（ドメイン）」を教えてくれる関数です
      • 例：dom(社員番号) = {1001, 1002, ...}, dom(年齢) = {0, 1, 2, ..., 120}

  2. 直積を作る

    • 「リレーションは、属性ごとのドメインからなる直積の有限部分集合」と言ってます。
    • 具体的に言うと：dom(A₁) × dom(A₂) × ... × dom(Aₙ)
    • つまり、「属性A₁の値」と「属性A₂の値」...のすべての組み合わせ（全パターン）を考えるということです。

  3. そのうちの一部だけを使う（部分集合）

    • 実際にテーブルとして存在するデータ（リレーション）は、その「全部の組み合わせ」ではなく、実際に登録されているデータ（行）だけです。
    • したがって、「リレーション R(A₁, A₂, ..., Aₙ) は、直積 dom(A₁) × dom(A₂) × ... × dom(Aₙ) の有限部分集合」という言い方になる。

結論：表とはまるで違う

特性 原因（数学的な理由）
重複レコードなし 集合だから、同じタプルは1個まで
レコードに順番がない 集合は順序を持たないから
カラムの順番も意味なし リレーションは、属性名（名前・年齢など）によって定義されているから
  • 「リレーション ≠ 表」
  • 「Excelの表っぽいけど、実は全然違う」
  • ここまで読んで「ふーん」ってなった人、正解です。
  • 無理に理解しなくても、今は「Excelの表と違うらしい」くらいでOK！

出典

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?