ゲーム開発日誌タイトル画像
IT技術部終了のお知らせ
Windows Server 構築

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



Twitterでシェア
Facebookでシェア
Google+でシェア
はてなブックマークに追加
Pocketで後で読む
投稿日時:2018/04/08 13:20:10
更新日時:

ここで解説する方法は実際にこのサイトで使われている手法です。データベースの設計は他にいろいろなやり方があります。ブログとありますが、普通のサイトを作る際も同様の手順です。
当記事の良いと思った情報を取り入れて、自分で考えながらサイト構築をするのよ。このカテゴリで紹介しているブログ構築方法が絶対ではないわ。
それではまずはデータベースを作成しましょう。
SQL Server 設計
SQL Server Management Studioを起動して画像のようにプラスのアイコンをクリックして展開します。

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

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

SQL Server 設計
データベースが作成されましたね。

データベースの設計は試行錯誤して保守しやすいように工夫したり、正規化して効率よく処理できるようにする必要があります。正規化とは簡単に説明しますと決まったレコード(データ)が挿入される場合、予め別のテーブルでそれらを管理してレコードの総容量を減らしつつ保守しやすくなる効果があります。データベースはこうしなければならないというのはありません。僕の見解ですが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の左にあるプラスのアイコンをクリックします。

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

列名データ型NULLを許容
big_category_idtinyintなし
big_category_namenvarchar(20)なし
big_urlrootvarchar(20)なし
sort_ordertinyintなし
SQL Server 設計
NULLを許容の説明ですが、データの未記入を許すかどうかです。未チェック(なし)なら必ずデータを入れなければなりません。

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

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

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

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

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

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

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

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

続いて小カテゴリーのテーブルを作りましょう。
大カテゴリーを作った時と同様に画面左側に表示されていますオブジェクトエクスプローラーからテーブルを右クリックして「テーブルを(T)」を選択します。
列名データ型NULLを許容
big_category_idtinyintなし
small_category_idtinyintなし
small_category_namenvarchar(20)あり
small_urlrootvarchar(20)あり
SQL Server 設計
注意するところは主キーが二つあることです。これを複合キーと呼びます。簡単に説明するとこの二つの主キーを使って単一のレコードを特定することです。2つ合わせて一つの主キーと言った方が分かりやすいかも。今は分からなくても、実際にシステムを稼働してみると分かってくると思います。

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

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


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

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

列名データ型NULLを許容
pkiji_idchar(7)なし
big_category_idtinyintなし
small_category_idtinyintなし
kiji_idsmallintなし
keywordsnvarchar(64)あり
titlenvarchar(64)なし
kiji_datanvarchar(MAX)なし
ogimagevarchar(127あり
descriptionnvarchar(200)あり
ins_datedatetimeなし
up_datedatetimeあり
public_flgbitなし
invisible_flgbitなし
SQL Server 設計


このテーブルの簡単な説明
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は記事を完全に非公開にします

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


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

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

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

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


ここで仕様の確認ですが、
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にすると決めました。
うーん。難しいというのか、説明が下手だというのか。
コラ!今は理解が半分でも運用してみればだんだんと分かってくると思います。次のページではプログラム側でデータベースのデータを拾ってくるチャプターです。
コメント一覧
お名前:

コメント:
left166533right
 表示されている6桁の数字を入力してください。
 (※スパムコメント対策です。)
00042512