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

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

自分用メモ

「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パターンがある。


備忘録メモ


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

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

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


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

関連記事

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

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