データベース設計入門#3 テーブル設計の手順【実戦形式で解説】
Summary
TLDRこのビデオスクリプトでは、データベースのテーブル設計のステップを実践的に紹介しています。システム要件の理解、概要設計、詳細設計の3ステップを説明し、テーブルの命名規則や外部キー制約の設定方法を解説します。さらに、ER図の作成と正規化、制約条件やインデックスの設定についても解説しています。このガイドは、データベース設計の基礎を学ぶ初心者から経験豊富なデザイナーまで幅広い層に役立つでしょう。
Takeaways
- 💡 システムの要件と機能を明確にすることがテーブル設計の第一歩。
- 📝 テーブル設計は3つのステップで進める: システムの要件把握、テーブルの概要設計、テーブルの詳細設計。
- 📊 テーブルの概要設計では、主要なテーブルとカラムの一覧をラフスケッチする。
- 🔄 テーブル名は複数形、カラム名は単数形にする命名規則を守る。
- 📑 テーブル名やカラム名には予約語を使わないように注意する。
- 🔧 カラムに型をつける際、VARCHARの桁数は2の累乗数を基本にする。
- 🛠 すべてのテーブルにID、created_at、updated_atの3点セットを追加する。
- 🔗 ER図を使って正規化とリレーションの設計を行う。
- 🛡 制約条件やインデックスを適切に設定し、データの整合性を保つ。
- 📈 インデックスは検索のキーになるカラムに付けるが、プライマリキーや外部キーには自動で付くので不要。
- 🔍 NOT NULL制約、ユニークキー制約、外部キー制約を可能な限りつける。
- 📝 論理削除と物理削除の違いを理解し、システム要件に応じて適切に外部キー制約を設定する。
Q & A
テーブル設計のステップは何ステップで構成されていますか?
-テーブル設計は3つのステップで構成されています。ステップ1でシステムの要件を把握し、ステップ2でテーブルの概要設計を行い、ステップ3でテーブルの詳細設計を行っています。
システムの要件を定義する際に重要なポイントは何ですか?
-システムの要件を定義する際、重要なポイントはクライアントとの要件の打ち合わせを通じて要件と機能を明確にすることです。
テーブル設計の概要設計では何を行なう必要がありますか?
-テーブル設計の概要設計では、テーブルの一覧と主要なカラムをガバッとリストアップし、ラフスケッチを作成する必要があります。
テーブル設計の詳細設計では何を行いますか?
-テーブル設計の詳細設計では、日本語のテーブル名やカラム名を英語に変換し、カラムにデータ型をつけ、ER図を書きながら正規化を行い、制約条件やインデックスを設定することが含まれます。
テーブル名やカラム名の命名規則には何がありますか?
-テーブル名やカラム名の命名規則として、使える文字は半角アルファベット、半角数字、アンダーバーのみ。テーブル名は複数形、カラム名は単数形にし、カラム名は雑な名前ではなく意味のある名前をつけることが挙げられます。
外部キーの命名ルールは何ですか?
-外部キーの命名ルールは、テーブル名の単数形にアンダーバーをつけ、その後に'id'をつけることです。例えば、'shops'テーブルの外部キーは'shop_id'という命名になります。
インデックスをつける目的は何ですか?
-インデックスをつける目的は、データベースの検索を高速化することです。検索のキーになるカラムや頻繁にアクセスされるカラムに対してインデックスを設定することが一般的です。
制約条件の種類には何がありますか?
-制約条件にはNotNull制約、Uniqueキー制約、外部キー制約の3つがあります。それぞれ必須入力の確認、重複しない値の保証、関連するテーブルに存在するレコードへの参照を保証するものです。
正規化とは何ですか?
-正規化とは、データベース設計の過程でデータを重複を避けずに整理し、効率的に保管するプロセスです。正規化はデータの整合性と保守性を高めることを目的としています。
データベース設計における予約語とは何ですか?
-データベース設計における予約語とは、データベースシステムで既に使用されているキーワードのことです。これらの予約語はテーブル名やカラム名として使用されることは避けるべきです。
テーブル設計の際に考慮すべき主な要素は何ですか?
-テーブル設計の際に考慮すべき主な要素は、システムの要件、機能一覧、テーブルの概要と詳細設計、正規化、制約条件の設定、インデックスの適用、命名規則の遵守などです。
Outlines
📝 テーブル設計の基本手順
この段落では、システムの要件を理解し、テーブル設計の3ステッププロセスを紹介しています。ステップ1ではシステムの要件を把握し、ステップ2では概要設計を進め、ステップ3では詳細設計を行っていくことが説明されています。特に、アマゾンのようなECサイトのテーブル設計を例に、テーブルの命名規則や外部キー制約の設定方法について解説しています。
🔠 日本語テーブル名の英語への変換と命名規則
ここでは、日本語のテーブル名やカラム名を英語に変換する際のポイントが説明されています。半角英数字とアンダースコアのみを使用し、テーブル名は複数形、カラム名は単数形にすることが推奨されています。また、カラム名には適切な命名規則を用い、予約語を避けるよう注意喚起しています。
🔄 カラムのデータ型指定と外部キーの設定
この段落では、カラムにデータ型を指定し、外部キーを設定する方法について解説しています。IDカラムは主キーとして自動インクリメントを設定し、作成日や更新日は日付と時間の情報を含むDATETIME型を使用することが推奨されています。また、テーブル間のリレーションをER図で表現し、正規化の重要性を強調しています。
🔗 外部キー制約とインデックスの適用
最後の段落では、外部キー制約を適切に適用することでデータの整合性を保証する方法について説明しています。また、インデックスを設定して検索効率を高めるポイントや、制約条件を設定する際の注意点についても触れています。ユニークキー制約やNULL許容の制約についても解説しており、物理削除と論理削除の選択肢に応じた設計の重要性を強調しています。
Mindmap
Keywords
💡テーブル設計
💡システムの要件
💡概要設計
💡詳細設計
💡正規化
💡ER図
💡外部キー
💡インデックス
💡制約条件
💡論理名と物理名
Highlights
テーブル設計の3ステップを実践形式で解説。
システムの要件を把握することがテーブル設計のステップ1。
アマゾンのようなECサイトのテーブル設計を例に説明。
機能一覧を定義し、それを基にテーブル設計を進める。
テーブルの概要設計では主要なテーブルとカラムを洗い出す。
シナリオに沿ってテーブルを洗い出す方法を提案。
日本語のテーブル名やカラム名を英語に変換するポイント。
カラム名の命名規則とデータベース予約語の注意点。
テーブルにid、create_at、update_atの3つのセットを追加する理由。
ER図を書きながら正規化を行う方法。
外部キーの命名ルールと1対n関係のテーブル設計。
n対n関係のテーブル設計における中間テーブルの作成。
インデックスの重要性と適用するカラムの選択基準。
制約条件の種類と適用時の注意点。
not null制約、ユニークキー制約、外部キー制約の設定方法。
論理削除と物理削除の選択と外部キー制約の関係。
テーブル設計の最終確認と改善のポイント。
コメント欄へのフィードバックの呼びかけと他の有用な動画の案内。
Transcripts
戦までです今回はテーブル設計の手順を
実践形式でお見せしますテーブルの命名
規則や外部キー制約の貼り方などとても
重要なポイントを解説しますので最後まで
動画をご覧下さい
それではテーブル設計の進め方を説明し
ますテーブル設計はこの3ステップで進め
ていきますまずは step 1で
システムの要件を把握しますそして
ステップ2ではテーブルの概要設計を行い
ますカラムの型をどうするとかユニーク
キーをどうするかとかそういう細かいこと
は次の step 3の方でやるので一旦
置いといてまずこのステップ2の時点では
ガバッと主要なテーブルと絡むの一覧を
書き出すラフスケッチの作業を行います
そしてステップ3でテーブルの詳細設計を
行います
それではステップ1としてシステムの要件
を把握していきましょう
今回はアマゾンのような ec サイトの
テーブル設計をやってみます設計の第一歩
はシステムの要件と機能を明確にすること
です今回はこんな感じでシステムの要件と
機能を定義しました今回のシステムの要件
としてはアマゾンのような ec サイト
ですそして機能一覧を見てみるとまずは
フロント画面で商品を検索したり商品の
詳細ページを見たりあとはマイページで
ログインしたり購入履歴を見たりすること
ができますそして商品を販売する側の管理
画面ではログインしたり商品の管理をし
たりあとは商品にカテゴリーを付けたり
することができます
こんな感じで設計を進めるステップ1とし
てはシステムの要件と機能を明確に定義
するところから始めます
ここをどう定義するかについては設計云々
ではなくてお客さんとの要件の木目の話に
なってきます
[音楽]
システムの要件と機能一覧が定義できたら
次はテーブルの概要を設計します
このステップ2のフェーズの完成形を先に
お見せするとこんな感じになりますカラム
の型や制約条件などの細かい設計は後回し
にしてとにかくテーブルの一覧と主要な
カラムをがバッド下記出せれば ok です
ツールは excel がオススメですが
ノートに手書きでも全然 ok です
では実際にやってみましょう
まずはテーブルの一覧を洗い出していくの
ですがやみくもに思いつきで書き出して
いくやり方だと必ず考慮漏れ設計漏れが
発生してしまいますではどうやって進める
のかというと先ほどの機能一覧を見ながら
シナリオに沿ってテーブルを書き出して
いきます
では一緒に行ってみましょうこの機能一覧
を見ながらシナリオを思い浮かべてみ
ましょう
この機能一覧では商品検索機能が一番上に
表示されていますがまだ商品が1軒も登録
されていないので先に商品を登録する筆が
ありますですのでシナリオで考えるときは
上から順番に見ていけばいいというもので
はなくてシナリオ頭に思い浮かべますこの
場合はまずは管理者としてログインをして
商品を登録してこちらの方から見ていき
ますそれでは管理者が商品を登録すると
いうシナリオをまずは考えてみますおっ
はじめにログインを行うためのテーブルが
必要になります
ですのでまずは店舗テーブルを用意し
ましょう
ここに店舗名とメールアドレスと
パスワードを持たせて店舗の管理者がログ
インを行います
次に商品や商品のカテゴリーを管理する
ためのテーブルも必要になります
ですので商品テーブルと商品カテゴリー
テーブルを追加します
次は一般ユーザーの使うフロント画面の方
のしなりを見ていきましょう
まずは商品検索や商品の詳細ページですが
これは先ほど管理者が登録した商品
テーブルを使って行いますですので新しい
テーブルの追加は必要ありません次に my
ページでログインしたり購入履歴を見たり
することができますこれを行うために
ユーザー様のログイン情報テーブル
users テーブルというのを追加し
ます
そして購入履歴テーブルも用意しますはい
こんな感じでシナリオを思い浮かべながら
思いつく限りのテーブルと絡むの一覧を
洗い出していきます
これで step 2の概要設定は完了
です
最後により詳細な設計を行っていき
ましょう
この最終フェーズではこの4つの作業を
行っていきます
まずは step 1ですが日本語の
テーブル名やカラム名を英語に変換します
次の3-2ではカラムに方をつけていき
ますそして3-3では er 図を書き
ながら正規化を行っていきます
正規化について分からないという方は前回
の動画で説明していますのでそちらの動画
をご覧下さい
動画の url は概要欄に貼っておき
ますそしてさんの選んでは制約条件や
インデックスをつける作業していきます
それでは一つずつ順番にやっていき
ましょう
[音楽]
まずは日本語のテーブル名やカラム名を
英語に変換します
では実際にこの日本語のテーブルを英語に
変換するとこうなります
英語に変換する際のポイントとしては
こちらの命名規則を参考にしてください
まず1点目ですが使える文字は半角
アルファベット半角数字アンダーバー
この3つだけですそして2点目ですが
テーブル名は複数形カラム名は単数形にし
てください
例えば商品テーブルの場合はアイテム
テーブルではなくてアイテムズテーブルに
なりますテーブル名は複数形だからです
そして絡むはすべて単数形になっています
例えばこちらのストックという在庫を表す
絡むですがこちらは複数形ではなくて単数
形のストックになっていますしています
このように他のテーブルを見てもテーブル
名は複数形から部は単数形
テーブル名は複数形絡むは単数形とこの
ような命名規則になっています
そして命名規則の3点目ですがよくやり
がちな水なのですが
カラム名はテキスト1テキストにのような
雑な名前にしないでください
それぞれ意味があるはずなのでちゃんと
このように一目で見て意味がわかるような
カラム名をつけましょうなお日本語の名前
を論理名
英語の名前を打つ決めと言いますのでこれ
も覚えておきましょう
論理名と物理目両方を20管理するのは
コストがかかるので慣れてきたら設計段階
から打つ姫1本で管理しても良いかと思い
ます
そしてもう1点
データベースには予約語というものがあり
ます
岐阜塗るリミットデートなどこれらの言葉
はテーブル名やカラム名として以前に別の
意味を持っていますそしてこれらは
データベースの世界ではようやく号と呼ば
れています
これらの言葉をテーブル名やカラム名に
使ってしまうと sql で構文エラーが
発生するなど面倒なことが起こりますので
予約後はテーブル名やカラム名としては
使わないようにしましょうろ約5このほか
にもたくさんありますのでデータベース
予約語で検索して一覧を何となく頭に
叩き込んでおきましょう
次に絡むに方をつけていきましょう
なおここからは excel だと作業し
にくいので mysql
workbench という無料のツール
を使います
ツールの詳細は概要欄に貼っておくので
使ってみてくださいでは早速解説していき
ます
カラムに方をつけた結果がこちらになり
ます
例えばアイテムズテーブルのネーム絡むの
方はバーチャルのに午後といったように
絡むに方をつけていきます津波にこの
250号という数字には意味があるので
覚えておきましょう
こちらがバーキャラの桁数を付ける時の
ルールですバーチャルの桁数は
24816といったように2の累乗数を
つけましょう
ただしこの256という数字だけは250
語に直してください
なぜかというと桁数が256以上だと
インデックスが張れなくなるからですです
のでバーキャラの桁数を決定するときは
基本的には2のルー上水にしつつも二ゴロ
だけはに午後に変換するというふうに覚え
ておきましょうなお桁数はちょっと余裕を
持たせた桁数にしておくのが良いと思い
ますそしてもう一点皆さんお気づきかも
しれませんがどのテーブルにも
id クリエイテックと at up デー
テッド at この3点セットを追加して
ありますこちらのテーブルにも id
グレーペット at updater っと
こちらのテーブルにも id と
クリエイト後 up デーテッド at
こちらのテーブルにもこの3点セット
こちらのテーブルにもこの3点セット
このようにすべてのテーブルに id
クリエイターとアップでデッドの3点
セットを追加してありますまず id に
ついてですがこちらはプライマリキー
つまり主キーになりますそしてかたは員と
形で auto increment を
設定してください
そして名前は id にしてください
ショップ id のような変な名前にし
ないでください次クリエイテックと at
と up デーてっと後ですがこちらは
作成1更新日になります
方はデートタイム型を指定してください
そして名前ですがクリエイテックとでー
とーとか up デーテッドデー等では
なくてなんとか at
命名してくださいこちらのデートタイム型
の場合は日付の情報だけではなくてタイム
つまり時間まで含めることができる方です
この2つのカラムはデートタイムがだです
ので名前もなんとかデートではなくてなん
とか at というふうに命名するように
しましょう
以上の内容を踏まえるとここまででこの
ような設計内容となっています次の
ステップではこれらのテーブルに
リレーション関係をつけながら er 図
を書いていきます
[音楽]
それでは er 図を書きながら外部キー
を追加していきましょう最終形態としては
こんな感じになります
まずはショップ stable と
アイテム stable を見ていき
ましょう
一つの店舗は複数の商品を持ちます
このように1対 n の関係の時は n 側
にショップ id のような外部キーを
追加します
この外部キーの命名ルールはテーブルの端
すーげーアンダーバー id にします
ですので今か大和 shops テーブル
の関数型なのでショップそれにアンダー
バー id をつけてショップ id と
しています shops id のような
複数形にはしないで下さい
先ほど primary key の
こちらの id の方の姉名ルールを説明
した時はショップ id のような命名で
はなくて単純
に id という名前にしてくださいと
言いましたがこのように外部キーの場合は
ショップ id のような名前になります
もう一つ1対 n の関係を見てみ
ましょう
ユーザー度テーブルとパーチェス
history ず底部です
ユーザーズテーブルというのはユーザーの
テーブルです
そしてパーチェス history ずで
ブルは購入履歴テーブルです1人の
ユーザーは複数の購入履歴を持ちますので
1対 n の関係になります1地帯 n の
関係の時は n 側に外部キーを持たせる
という話でしたのでユーザーは id を
ここにも出せています
この名前はユーザーズテーブルを単数形に
したユーザー
それにアンダーバー id をつけて
ユーザー id とこのような命名ルール
に従っていますそして購入履歴テーブルに
は購入した商品の id を持たせたく
なりますが商品を複数
購入することのことを考えるとこちらの
購入履歴テーブルにアイテム id を
持たせるのではなくて購入履歴明細
テーブルとして切り出した上でこちらに
商品のアイテム id を持たせる方が
良いでしょう
ですので購入履歴テーブルから購入履歴
明細テーブルを1対 n の関係で
切り出して正規化しています
そしてこちらの購入利益迷彩テーブルと
アイテムテーブルを1対1のリレーション
を張っていますこのように er 図を
引きながら
正規化するという作業も行っていきます
最後に商品カテゴリと商品テーブルの関係
ですがここは n 対 n の関係になり
ます n 対 n 場合は中間テーブルを
作ってこのように中間テーブルに外部キー
を持たせますアイテムズセーブルに対して
のアイテム id アイテムカテゴリーず
テーブルに対してのアイテムカテゴリー
id をこのように持たせます
最後に制約条件やインデックスをつけて
いきます
まずインデックスですがインデックスとは
検索を早くするためのものですどういう
カラムにインデックスをつけるかというと
検索のキーになるカラムに付けます津波に
プライマリティーや外部キーには付けなく
て ok です
なぜならば自動で作っからです
それでは今回のケースを見ていきましょう
今回の要件を考慮すると
商品検索という機能があります
これは商品名で検索する機能ですので
アイテム stable の name
このカラムにインデックスを貼りましょう
今回の場合はこれだけで ok です最後
にテーププールに制約をつけましょう
成約にはこの3つがあります not
null 制約
ユニークキー制約
外部キー制約です
この3つの制約は可能な限りつけるように
しましょうそれでは1つずつ解説していき
ますまずは not null 制約です
今回の場合全て必須入力項目ですので
すべてのカラムに not null を
つけます
このようにつけられるものなら可能な限り
制約をつけるようにしましょう
では逆にどういう時に not null
制約をつけないかというと例えば購入履歴
テーブルにメモのような絡むがあったとし
ますメモというのは例えば備考として領収
書をくださいといったようなあってもなく
てもどちらでも良いような絡むですこう
いった場合は not null では
なく塗る許可となります次にユニークキー
制約について解説します
ユニークキー制約とは値が重複しないこと
を約束する誓約です
重複しない項目には可能な限りユニーク
制約を貼りましょう
今回の場合はまずは商品カテゴリーの
カテゴリ名です例えばお酒とか衣料品と
いった商品カテゴリの名前が重複して登録
されるのは防ぎたいので今回は商品
カテゴリ名
つまりアイテムカテゴリーずテーブルの
name このカラムにユニークキー制約
を貼ります次に商品名
これも重複して登録されるのは防ぎたいの
でアイテムずテーブルの name この
カラムにユニークキー制約を貼ります
最後に店舗の名前
これも重複してはいけない項目なので
shops テーブルの name この
カラムにユニークキー制約を貼ります
一方でユーザーテーブルのネームこれは
同姓同名がありえるのでユニークキー制約
は貼りません最後に外部キー制約について
解説しますん
外部キー制約とは relation 先
にレコードがあることを保証する誓約です
これも可能な限り制約をつけるようにし
ましょう
今回の場合だと外部キーになっているから
6これらには全て外部キー制約を貼ります
例えばユーザー id とかパーチェス
history id 後はアイテム id
とかショップ id これらはすべて外部
キーですので外部キー制約を張って
リレーション先にレコードがあることを
保証しますただし購入履歴テーブルの
ユーザー id このケースを考えてみ
ますもしもユーザーが退会した時に
ユーザーの情報を論理
削除するのか移り削除するのかによって
外部キー制約の貼り方が変わってきます
物理削除というのはレコード本当に削除
するやり方なので外部キー制約が保てなく
なります一方で論理削除というのは
ユーザーテーブルに削除フラグを設けて
論理的に削除するやり方なので外部キー
制約は保てますこの論理削除なのか物理さ
ことなのかについては個人情報の保護方針
など設計以前にシステム要件が絡んでくる
部分ですので
クライアントに確認をとって要件を改めて
整理しながら適切な設計にしましょう
[音楽]
では今回のまとめですテーブル設計はこの
3ステップで行いましょう
まずはステップ1としてシステムの要件を
把握します
設計の第一歩はシステムの要件と機能を
明確にすることからでした
次にステップにとしてテーブルの概要を
設計しますここはやみくもにテーブルを
洗い出すのではなくてシナリオに沿って
アライドするのがポイントでした
最後のステップ3でテーブルの詳細を詰め
ていきますステップ3は4段階あってまず
は日本を英語に変換します
次に絡むに方をつけていきます
次に er 図を書きながら正規化も行っ
ていきます
そして最後に制約条件やインデックスを
つけていきますはい今回はテーブル設計の
手順を解説しました設計のやり方について
は異論や反論
違う考え方もあると思いますのでコメント
欄に書き込みいただけたら嬉しいですまた
この動画が役に立ったという方は高評価の
グッドボタンそして sns や友達への
共有もお願いしますこうチャンネルでは他
にも役に立つ動画を配信していますので
ぜひそちらも覗いてみてください
5.0 / 5 (0 votes)