Webサービスのテーブル設計について

この記事は7分で読めます

自分用メモ

「Webサービス テーブル設計」で調べてみる

※参考:ウェブカツWebサービス部

DBの基礎知識


①DB

上記の画像の左にある「kyabahaken」など
例えるなら本棚。

②テーブル

「kyabahaken」の下にある「anken」や「ankenrireki」
「tenpo」「tenpo_favo」「tenpo_ng」「users」「users_favo」のこと。
例えるなら顧客台帳。名前や生年月日、住所などの情報が詰まってるファイルを
「本棚」であるDBにしまうイメージ。

③カラム

例えるなら顧客台帳の項目。
歯医者の顧客台帳の場合は、氏名、年齢、住所、前回の診療内容など。
縦の列を表してる。

④レコード

例えるなら顧客台帳にのってる、個々人の情報。
カラムに乗ってる情報が氏名、年齢、住所、電話番号だとしたら
「早乙女乱馬 17 東京都練馬区◯◯町1-1-1 090-111-111」みたいな感じ。
横の行を表してる。


※参考:データベース(DB)とは?CRUDやSQL、作成方法も解説
※参考:リレーショナルデータベース(RDB)とは?四大命令や主キー外部キーも解説

テーブルの具体例


キャバクラの派遣サービスの場合は、
以下の3つが関わってくるので
これらを軸にテーブルを作る必要がある。

・ユーザー(女の子)
・店舗(キャバクラ)
・案件

例えば、以下のテーブルを作成する。

users(女の子)

ユーザーID
名前
生年月日
電話番号
住所
最寄り駅
メールアドレス
パスワード

anken(案件)

案件ID
店舗ID
ユーザーID
日にち
募集人数
時給
勤務開始時間
備考

tenpo(店舗)

店舗ID
店舗名
店舗代表者
メールアドレス
パスワード
電話番号
住所
最寄り駅
業種
ヘアメイク
派遣到着時間
派遣到着時間2回目
税金
厚生費
貸衣装
送迎代
送迎範囲
年齢確認書類
注意事項

anken_rireki(シフト履歴)

シフトID
案件ID
ユーザーID

user_favo(ユーザーお気に入り)

ユーザーID
店舗ID

tenpo_favo(店舗お気に入り)

ユーザーID

tenpo_NG(店舗NGリスト)

ユーザーID

テーブル作成時のお決まりカラム

なお、各テーブルの最後のカラム3つは、
以下の3つを「お決まり」として入れる必要がある。

・delete_flg(会員と非会員をフラグ分けできる)
・create_date(レコード作成日)
・update_date(レコード更新日)



SQL鬼練11 テーブル同士の内部結合


これまでは「1つのテーブル」に対してデータを取ってくるためのSQLだった。
※参考:ウェブカツPHP・MySQL部

しかしこれだと、メルカリや楽天の「商品の詳細ページ」で
「商品情報」と共に「その商品を登録した売り手のユーザー情報」
を表示したい時に、上手くできない。

この2つを表示するには、ユーザーが登録した他の情報
(ユーザーに紐付いている他のテーブルの情報)を取得する必要がある。

★ヒント

「あるテーブルのレコードに紐づく他のテーブルを取得する」のがポイント。
この場合は「JOIN句」を使う。

なお、普通のSELECT文でも取得はできる。
①該当のユーザー情報をDBから取得する
②そのユーザーIDを元に、今度は商品テーブルをSELECT文でまた検索する。

ただしこの場合だと、2回もDBへアクセスする必要がある。
時間がかかるので推奨はできない。


productsテーブルの「FOREIGN」は
FOREIGN KEY」のこと。「外部キー制約」という。
テーブル設計図を作成した際、外部キー制約をつけたカラムの名前には
ユーザーID(FK)」という感じで「FK」と付けておく。
(テーブルを作成する際に、カラム名に「FK」と付ける訳ではないので注意)


テーブルの設計図を「ER図」という。
ER図は「書き方」「書式」が決まっており、
「テーブルの構成」と「テーブル同士の関係性」が
パッと分かるように書くものである。


①user_idをつけよう

「ユーザーが商品を登録する」場合、
その「登録した商品の情報」をDBに保存する必要がある。
なので、商品テーブルを作る必要がある。

ただし、商品テーブルの情報だけ見たら、後から見返した時に
「この商品ってどのユーザーが登録した商品?」となり、
ユーザーと商品の関連が分からなくなってしまう。

なので、商品テーブルに「この商品はどのユーザーのものか」を
識別できるように「user_id」を付ける必要がある。

現実世界での「顧客台帳」と「商品台帳」でイメージすrばいい。
商品台帳には「どの顧客の商品か」を識別するものが必要。


FOREIGN KEY制約の設定


①FOREIGN KEY制約(外部キー制約)とは
親テーブルと子テーブルの2つの間で
データの整合性を保つために設定される制約。

子テーブルにデータを追加するとき、FOREIGN KEY制約が設定されたカラムには
親テーブルのカラムに格納されてる値しか格納できなくなる。
(親テーブルに存在しない値を含むデータを追加するとエラーになる。)

②FOREIGN KEY制約の使い方
FOREIGN KEY制約の設定は、子テーブル側で行う。
以下の書式を用いる。

create table db_name.tbl_name
(col_name data_type, …
FOREIGN KEY [index_name] (col_name, …)
REFERENCES tbl_name (col_name, …)



親テーブルと子テーブルは以下のようになる。


・親テーブル
create table 親テーブル名 (親カラム名 データ型 PRIMARY KEY);


・子テーブル
create table 子テーブル名
(子カラム名 データ型,
index インデックス名(子カラム名),
FOREIGN KEY 外部キー名(子カラム名) REFERENCES 親テーブル名(親カラム名))



③外部キーに関する情報を取得する
INFORMATION_SCHEMA.KEY_COLUMN_USAGE」を参照する。
これで外部キーに関する情報を取得できる。


②外部キー制約

外部キー制約を付けることで「テーブル同士の整合性」が保ちやすくなる。

今回のように、商品テーブルとユーザーテーブルを紐づけて外部キー制約をつければ
すでに商品を登録しているユーザーの情報を削除できないように出来る。
(ユーザー情報が削除され、商品の情報だけ残っていたらおかしい。)

外部キー制約は、ユーザー情報を削除する前に
紐付いている商品を削除しておけば削除できるようになっている。
もしもユーザーが退会したら、
まずはユーザーが既に登録した商品レコードを全て削除し、
その上でユーザー情報を削除すれば、きちんと削除できる。

実際のところは、後々の問い合わせ対応のために
退会してからもしばらくはユーザー情報を残さないと困るので
物理削除」ではなく「論理削除」という方法を用いている。


③phpMyAdminでの外部キー制約

まずはFOREIGN KEY制約はつけず、普通にproductsテーブルを作成。
→やった


作成したら「テーブルの構造」タブから、
user_idカラムの右側にある「その他」をクリックして、
インデックス」を付与する。
(musqlでは外部キー制約を付ける際、必ずインデックスを付ける必要があるので)


すると「インデックス」という下欄にuser_idが追加される。
これでuser_idカラムにインデックスが付いてる事がわかる。


次に「リレーションビュー」をクリックして画面を開く。


すると外部キーを登録する画面が出てくる。

以下の項目を入力する。
保存すると外部キーが出来上がっている。

制約名:作った外部キー制約に名前を付けたい場合に入れる。(空でもOK)
ON DELETE:RESTRICT(エラーになる)
→外部キーのカラムと紐づけた親テーブルのレコードを削除しようとした時の挙動を決める。
ON UPDATE:RESTRICT
→外部キーのカラムと紐づけた親テーブルのカラムを更新しようとした時の挙動を決める。
データベース:外部キーを付けるカラムと紐付けるデータベース名を指定。
テーブル:外部キーをつけるカラムと紐付けるテーブル
カラム:外部キーをつけるカラムと紐付けるテーブルのカラム

RESTRICT,CASCADE,SET NULL,NO ACTIONの違い


・RESTRICT:エラーになる
・CASCADE:参照先の変更に追従する
・SET NULL:NULLに置き換わる
・NO ACTION:RESTRICTに同じ





④内部結合(INNER JOIN)


テーブルの結合は、以下の形で行われる。
SELECT * FROM 結合元のテーブル名(左) JOIN 結合先のテーブル名(右) ON 両テーブルの結合条件

結合にはJOIN句を使う。
JOINで結合先のテーブルを指定し、ONより後ろに、どの条件で結合するのかを記述する。

結合の種類には、「JOIN」「LEFT JOIN」
「RIGHT JOIN」「FULL JOIN」の4パターンがある。

DBのテーブル設計鬼練1


DBのテーブル設計は、初心者のうちは難しい。
「数をこなさないと慣れないもの」だから。
サービスをいくつか考え、そのテーブル設計を何度も考えることで、
力がついてくる。

★Twitterに出てくる登場人物
ユーザー
ツイート(コンテンツ)
管理者

まずは「そのサービスに出てくる登場人物」を考えるのが大事。
→しもたかろぐの場合は、ユーザー、店舗コンテンツ、管理者

DBのテーブル設計鬼練2


★Twitterの広告システムに出てくる登場人物
ユーザー
管理者
広告(コンテンツ)


DBのテーブル設計鬼練3


★Facebookの登場人物
ユーザー
コンテンツ:投稿
管理者
(金)

Instagramでも同じ:写真が「投稿」にあたる?

DBのテーブル設計鬼練4


★メルカリの登場人物を考える
ユーザー:売り手、買い手
商品
管理者


テーブル設計を考える上では、
「売り手」「買い手」をどうDBでデータを持つのかを考える必要がある。
(DBへの記録方法を考える)

また「管理者」に関しても、
「問い合わせ管理者」「違反報告チェック管理者」と
細かく分類ができる。
→この辺りも、DBでどうデータを持つか考える必要がある。

どんなサービスでも、以下の4つで考える。
①ユーザー
②管理者
③商品
④金

DBのテーブル設計鬼練5(情報洗い出し)


Twitterのユーザー情報を洗い出していく。

電話

名前
email
パスワード

背景画像パス
プロフ画像パス
自己紹介
場所
ウェブサイト
生年月日

画像はDBにそのまま保存できない。
画像はサーバーのどこかのディレクトリに置いておいて、
DBには「その画像のパス」を保存する。

DBのテーブル設計鬼練6


メルカリのユーザー情報を洗い出す。

ニックネーム
メールアドレス
パスワード:メルカリのパスワードを参考に、英字と数字両方を含む8文字以上にする。
パスワード(確認)

(ここからは、「本人確認」のための項目である。)
お名前全角性
お名前全角名
お名前カナ性
お名前カナ名
生年月日:年
生年月日:月
生年月日:日

今回は、もうちょい細かく情報を細分化してみる。
「名前」は、「漢字の名字」「漢字の名前」
「カナの名字」「カナの名前」と4つに分ける考え方がある。
生年月日も、「年」「月」「日」に分ける考え方がある。

①実際に確認してみると…


・名前
・名前カナ
・生年月日
・郵便番号
・都道府県
・市区町村
・番地
・建物名

・プロフィール名(HN)
・メッセージ

※「住所」に関しても、「都道府県」「市区町村」「番地」で分ける方法がある。

②情報の洗い出し方

今回はは「既にあるサービス」から項目を洗い出してるが、
実際は「これから作るサービス」「既にあるサービスに追加する機能」から、
「DBへ保存しておく必要のある情報」を洗い出す必要がある。

大事なのは「DBへ保存しておく必要のある~」。

まずは「サービスに必要な登場人物」を洗い出し、
次に「そのサービスに必要な情報」を洗い出す。

③サービスに必要な情報の洗い出し方

サービスに必要な情報には、以下の2種類がある。

①サービスで表示するために必要な情報
(ユーザーが見れる「表側」の情報)
②サービスの管理のために内部で必要な情報
(管理者しか見れなかったり、サービス運営のために内部だけで必要になる「裏側」の情報)

「表側」の情報に関しては、「画面設計」で
画面のワイヤーフレームを作っていくと分かってくる。
先に画面設計して「視覚化」した方が情報が洗い出しやすくなる。
→本当?先にある程度テーブル設計固めた方が、効率良くないか?

もっとも、慣れてきちゃえばさほど項目数がないものでも、
頭の中でワイヤーフレームが思い浮かぶので、
頭の中だけで必要な情報がパパッと洗い出せてしまう。

④内部に必要な情報とは?

①ユーザーIDなどの情報一つ一つに割り振っておくID
②情報が登録された日時:created_at
③情報が更新された日時:updated_at
④情報を表示、非表示にするために区別するためのフラグ
⑤削除された情報なのかどうかのフラグ:delete_flg

他には、テーブルを結合する時に必要な「外部キー」も当てはまる。

⑤DBに保存すべき情報かを洗い出す

基本的にDBへ保存をする情報は、以下の5通り。
(※ウェブカツのトップページにある「利用者の声」は、当てはまらない)

①永久的にしたいもの(ずっと保存しておきたい)
②更新頻度の高いもの
③ブラウザなど、閲覧ツールが変わっても同じように表示したいもの
④ユーザーのもの
⑤ユーザー自身に操作させて情報を操作させたいもの

DBのテーブル設計鬼練7


メルカリの商品情報を洗い出す。

出品画像パス(1~10)
商品名
商品の説明
カテゴリー
商品の状態

備忘録メモ


マッチングサービス」を作るならどうなるだろ?
フリマ:買い手、商品、売り手
キャバ派遣:女の子、案件、店舗

何かを軸に、二人の人が仲介を行なってる感じ。

自分が「仲介サービス」を作るとしたら?
※参考:ポートフォリオのジャンルや制作過程、注意点や必要な機能


  • このエントリーをはてなブックマークに追加
  • LINEで送る

関連記事

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。