初めてのブログシステムを作るためのSQL ServerのDB設計と構築方法
ここで解説する方法は実際にこのサイトで使われている手法です。データベースの設計は他にいろいろなやり方があります。ブログとありますが、普通のサイトを作る際も同様の手順です。
当記事の良いと思った情報を取り入れて、自分で考えながらサイト構築をするのよ。このカテゴリで紹介しているブログ構築方法が絶対ではないわ。
データベースの項目を右クリックして「新しいデータベース」を作成します。
データベース名の項目に任意の名前を付けてください。ここでは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 | なし |
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) | あり |
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にすると決めました。
うーん。難しいというのか、説明が下手だというのか。
コラ!今は理解が半分でも運用してみればだんだんと分かってくると思います。次のページではプログラム側でデータベースのデータを拾ってくるチャプターです。