初めてのブログシステムを作るためのSQL ServerのDB設計と構築方法

---

---

---

それではまずはデータベースを作成しましょう。 SQL Server Management Studioを起動して画像のようにプラスのアイコンをクリックして展開します。

データベースの項目を右クリックして「新しいデータベース」を作成します。

データベース名の項目に任意の名前を付けてください。ここではwebsitedbという名前を付けました。ちなみに使える文字は大文字、小文字、記号、数字などあらゆる文字を使うことができますが、記号はアンダーバー(_)以外は使わない方がトラブルがなくてよいと思います。

データベースが作成されましたね。

データベースの設計は試行錯誤して保守しやすいように工夫したり、正規化して効率よく処理できるようにする必要があります。正規化とは簡単に説明しますと決まったレコード(データ)が挿入される場合、予め別のテーブルでそれらを管理してレコードの総容量を減らしつつ保守しやすくなる効果があります。データベースはこうしなければならないというのはありません。僕の見解ですが20年前ですとデータベースの設計は正規化が必須でしたが、近年のコンピュータは性能の進化がめざましく、あまり容量やリソースを気にしないでも十分運用していけます。

ただし、多く正規化すると保守するときに構造が理解しにくくなったりするので自身で度合いを決めて設計することをお勧めします。何事も適度に。

今回は自分のサイトで実際に運用されている設計を公開したいと思います。

今回運用したいサイトの構造は
http://yigao.jp → 第一階層
http://yigao.jp/programming/ → 第二階層
http://yigao.jp/programming/csharp/ → 第三階層
http://yigao.jp/programming/csharp/1/ → 第四階層

上記のように第四階層で記事を管理したいと思います。
「programming」に位置するのが大カテゴリー
「csharp」に位置するが小カテゴリー
「1」に位置するのが記事ナンバーです

SQL Serverでテーブルを作ってみよう

データベース名websitedbの左にあるプラスのアイコンをクリックします。

ではまず作成したデータベースに大カテゴリーを管理するテーブルを作りましょう。テーブルの項目に右クリックでテーブルを選択します。

列名	データ型	NULLを許容
big_category_id	tinyint	なし
big_category_name	nvarchar(20)	なし
big_urlroot	varchar(20)	なし
sort_order	tinyint	なし

NULLを許容の説明ですが、データの未記入を許すかどうかです。未チェック(なし)なら必ずデータを入れなければなりません。

tinyintは0から255までの数字を記入できます。第二階層で255を超える大カテゴリーを作る予定でしたらtinyintより上のsmallintを設定するとよいでしょう。

ただし、大カテゴリーはプログラム側で大カテゴリーの分だけコントローラーと呼ばれるファイルを作成しなければならないので、あまり増えすぎると後で苦労します。

列名を簡単に説明
big_category_idは数字のIDを記入する項目です
big_category_nameは大カテゴリーの名前を記入する項目です
big_urlrootはURLになる文字列を記入する項目です
sort_orderは大カテゴリーの順番を記入する項目です

ここで主キーを設定します。一番上にあるbig_category_idを右クリックして「主キーの設定(Y)」をクリックします。主キーとは同じ値を許さない唯一無二のデータを入れてくださいというカラム(列)です。

左側に鍵のアイコンが表示されるとOK。

ここまで理解したらテーブルを保存しましょう。
Ctrl + S で保存します。

名前の選択ウィンドウが出ましたらテーブル名を付けることができます「c_big_category」と名付けました。

この先頭にある「c_」ですがこのテーブルは他のテーブルに繋ぐ(connect)ことを前提として作られていますという分かりやすい明示です。

続いて小カテゴリーのテーブルを作りましょう。
大カテゴリーを作った時と同様に画面左側に表示されていますオブジェクトエクスプローラーからテーブルを右クリックして「テーブルを(T)」を選択します。

列名	データ型	NULLを許容
big_category_id	tinyint	なし
small_category_id	tinyint	なし
small_category_name	nvarchar(20)	あり
small_urlroot	varchar(20)	あり

注意するところは主キーが二つあることです。これを複合キーと呼びます。簡単に説明するとこの二つの主キーを使って単一のレコードを特定することです。2つ合わせて一つの主キーと言った方が分かりやすいかも。今は分からなくても、実際にシステムを稼働してみると分かってくると思います。

Ctrl + Sでテーブルを保存します。「c_small_category」としました。

列名を簡単に説明
big_category_idは大カテゴリと連結するためのIDです
small_category_idは小カテゴリーの名前を記入する項目です
small_category_nameは小カテゴリーの名前を記入する項目です
small_urlrootは小カテゴリのURLになる文字列を記入する項目です


ここで注目、サイドにあるオブジェクトエクスプローラーで保存したはずのc_small_categoryのテーブルが見つかりません。そんな時は、オブジェクトエクスプローラーのぐるっと円を描いている更新ボタンを押しましょう。

続いてメインの記事データのテーブルを作成します。

列名	データ型	NULLを許容
pkiji_id	char(7)	なし
big_category_id	tinyint	なし
small_category_id	tinyint	なし
kiji_id	smallint	なし
keywords	nvarchar(64)	あり
title	nvarchar(64)	なし
kiji_data	nvarchar(MAX)	なし
ogimage	varchar(127	あり
description	nvarchar(200)	あり
ins_date	datetime	なし
up_date	datetime	あり
public_flg	bit	なし
invisible_flg	bit	なし

このテーブルの簡単な説明
pkiji_idは非常に分かりづらいのでなくても構いませんが7桁の数字で上2桁が大カテゴリー、続く2桁が小カテゴリー、最後の3桁が記事ナンバーとなります。 「0102001」とデータがあった場合、「01が(大カテゴリー)」続く「02が小カテゴリー」続く「001が記事ナンバー」という具合です。

大カテゴリーと小カテゴリーが3桁の場合、pkiji_idのサイズもchar(8)やchar(9)などとしなければなりません。

kiji_idはtinyintではなくsmallintを使っています。これはそのカテゴリーの記事が255を超えることを想定しているためです
keywordsはHTMLのメタタグでキーワード検索で使う文字列です
titleは記事のタイトルです
kiji_dataは記事の本文です
ogimageとはその記事のトップ画像です。SNSに記事のURLを貼ると、ここで指定した画像がSNSのタイムラインで表示されたりします
descriptionはこの記事の説明を載せる項目です
ins_dateは記事作成日時
up_dateは記事更新日時
public_flgは新着情報としてサイトトップに記事を公開するのかを指定します。記事を書き終えた後にONにします
invisible_flgは記事を完全に非公開にします

Ctrl + Sでテーブルの名前を「m_kiji」としました。
先頭の「m_」はマスター(master)で主力を意味します。一つまたは複数の「c_」テーブルと連結される側です。


実際にデータを入れてみましょう。 c_big_categoryのテーブルを右クリックして「上位200行の編集(E)」をクリックします。 お好きな大カテゴリーを必要な数だけ記入します。

続いて小カテゴリーにもデータを入力します。
big_category_idが「1」はプログラミングとなっており、その子であるsmall_category_idが更に小分けされたカテゴリーとなっています。

プログラミング(大カテゴリー)の中のプログラム言語(小カテゴリー)といった具合です。

ダミーデータをm_kijiのテーブルに入れます。

ここで仕様の確認ですが、
big_category_id = 1 は プログラミング(大カテゴリー)
small_category_id = 1 は C# (小カテゴリー)
kiji_id = 1 は 記事ナンバー1です。

つまり上記の記事データはプログラミング言語C#の最初の記事となります。
URLはhttp://yigao.jp/programming/csharp/1/となります。
ではhttp://yigao.jp/programming/csharp/とアクセスした時に表示する記事はどうしたらよいでしょうか。

ここでは一つの方法として
big_category_id = 1
small_category_id = 1
kiji_id = 0
として対応することにしました。

この要領でhttp://yigao.jp/programming/の大カテゴリーに直接アクセスした時に表示する記事は
big_category_id = 1
small_category_id = 0
kiji_id = 0
としてデータを入れたら対応できそうです。

大カテゴリーまたは小カテゴリーの記事を表示する場合は必ず記事ナンバーを0にすると決めました。