設計の全体像
設計は3回、粒度を変えて行う
前回、1枚の表に全部入れると事実が割れることを見ました。では、どうやって分ければいいのか。行き当たりばったりに分けるのではなく、粗いところから細かいところへ、3回に分けて決めていきます。
| 工程 | 決めること | 成果物 |
|---|---|---|
| 概念設計 | 何を管理するのか | エンティティの一覧 |
| 論理設計 | どんな項目を持ち、どう関係するのか | ER図・列と関係の一覧 |
| 物理設計 | どのデータベースに、どんな型で作るのか | CREATE TABLE 文 |
概念設計 — 名前だけ決める
最初は、扱うものの名前を並べるだけです。多店舗ECなら「店舗」「商品」「顧客」「注文」「在庫」といったところでしょう。
この段階では列も型も考えません。紙とペンで足りるのが概念設計です。ここで挙げ漏らしたものは、あとの工程で全部やり直しになるので、粗くても広く挙げます。
論理設計 — 項目と関係を決める
次に、それぞれが何を持つかを決めます。店舗なら「店舗名」「都道府県」。そして、店舗と注文はどう繋がるのかを決めます。
ここでもまだデータベース製品の話は出てきません。PostgreSQL だろうと MySQL だろうと、論理設計の結果は同じになります。
物理設計 — 型とキーを決める
最後に、実際に動くものにします。「店舗名」は text なのか varchar(100) なのか。id は integer なのか bigint なのか。ここで初めてデータベース製品の都合が入ってきます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE branches (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
opened_on date
);なぜ分けるのか
いきなり CREATE TABLE から書き始めると、必ず手戻りします。型を決めながら「そもそもこのテーブルは要るのか」を考えることになり、頭が2つのことを同時に処理できないからです。
工程を分けるのは、一度に1種類のことだけ決めるためです。
NOT NULL は論理設計の結論
もうひとつ大事なのが NOT NULL です。これは「この項目は必ず値がある」という論理設計の結論を、物理設計の書き方に落としたものです。
店舗名は必ずあります。しかし開店日は、これから開く店なら未定かもしれません。だから opened_on には NOT NULL を付けません。値が無いことを許すかどうかは、型と同じくらい大事な設計の決定です。
型と確認用の表
integer は整数、text は文字列、date は日付です。列をカンマで区切り、主キーは PRIMARY KEY、値を必須にする列は NOT NULL と書きます。
データベースの構造は information_schema という特別なスキーマに記録されます。通常の表と同じように SELECT できます。
SQL クエリ
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'branches'
ORDER BY ordinal_position;column_name は列名、data_type は型、is_nullable の YES は NULL を許すこと、NO は必須であることを表します。ordinal_position は列を定義した順番です。自分で作る表の置き場所が public です。演習の確認 SQL は用意済みなので、列の設計を担当します。
手を動かす
概念設計で挙げた「店舗」を、物理設計まで一気に進めてみます。そのうえで、自分が作ったテーブルの姿をデータベース自身に聞いてみます。
概念設計で挙がった「店舗」を、物理設計まで進めます。次の4列を持つ stores テーブルを作ってください。
id—integer、主キーname—text、必ず値があるpref—text、必ず値があるopened_on—date、未定でもよい
作ったら、information_schema から stores の column_name、data_type、is_nullable を、テーブル定義に書いた順 (ordinal_position の順) で取り出してください。
表の外枠と列の確認は用意済みです。4列の型と必須性を定義してください。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
-- まだ何もありません。ここに最初のテーブルを作ります。
期待される出力
| column_name | data_type | is_nullable |
|---|---|---|
| id | integer | NO |
| name | text | NO |
| pref | text | NO |
| opened_on | date | YES |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い