システム設計の話 その12・テーブル設計 コードマスタ [システム設計の話]
さて、テーブル設計の最後テーブル、コードマスタです。
プログラムでは、ある状態を識別する場合、数値に意味を持たせて、その値で判断することが良くあります。
例えば、性別の場合は、1=男性、2=女性、と言った感じです。
こういった判断のための値を、プログラム中にそのまま書いてしまうのは、あまり良くないとされています。
個人で作るプログラムなら良いのですが、仕事で作成するシステムのプログラムとなると、自分以外の人がプログラムを修正する場合や、他のプログラムとの共通化を考えると、きちんと管理する必要があるからです。
Constで宣言する方法もよく使われますが、データベースを使用するシステムの場合、テーブルに入れておくと便利な場面が多々あります。
データの区分コードに対応する名称を付けて、SELECT結果を得たい場合などは、テーブルを結合する事で名称を取り出すことができます。
ただ、コードの種類ごとにテーブルを作成すると、テーブルの数が増えて管理が大変になります。件数が多い場合は個別テーブルにした方が良いですが、だいたいの場合、数件であることが多いです。
こういった細々したコード情報をまとめて管理するのが、コードマスタと言うことになります。
以上、コードマスタについて云々書きましたが、今回考えてるシステムの内容では、実は使うあてがありません。
もしかしたら使うかもと思って、テーブル一覧にあげておいただけなの。
ですので、今回はコードマスタの一例としてご覧下さい。
キーは「グループCD」と「CODE」項目です。
項目名 | データ型 | 桁数 |
グループCD | 十進型 | 整数3桁 |
CODE | 十進型 | 整数5桁 |
グループ名 | テキスト型 | 100文字 |
コード名 | テキスト型 | 100文字 |
数値 | 十進型 | 整数8桁、小数4桁 |
文字列 | テキスト型 | 100文字 |
備考 | テキスト型 | 100文字 |
登録日 | 日付時刻型 | |
変更日 | 日付時刻型 | |
削除 | 十進型 | 整数1桁 |
例えば、こういう感じで登録します。
先ほどの性別例は、グループCD=2のようになります。
グループCD=1 AND CODE=数値 と言う条件で、コード名を取り出します。
グループCD=1の消費税率の例は、消費税率のパーセント数を登録しています。
こうしておけば、消費税率が変わったとしても、ここの値を変更すれば、以後そのパーセントで計算されるように、プログラムを作ることができます。(実際の消費税率変更の対応は、そんなに簡単にいきませんが)
グループCD=3の送料の例は、送り先の地区の名称とともに、その地区によって決まる送料代金の値も一緒に持たせています。
このような内容を一緒に登録しておくことで、例えば入力画面で送り先地区をユーザーが選択したら、送料を表示させ、その金額もあわせて登録されるようにする、と言った処理が可能になります。
「数値」「文字列」項目のように、任意で登録できる項目を設けることで、いろいろな使い方ができるようになります。
あと、蛇足ではありますが、このコードマスタテーブル、「グループ名」項目をテーブルの正規化という考え方でとらえると、冗長している項目となります。
同じ内容が複数行に表示されているので、ぱっと見て無駄な感じがするかと思います。
「正規化」と言う考え方だと、こういった項目は別テーブルにするのが正しいことになります。
「グループ名」項目はこのコードマスタテーブルには持たず、別に「グループCDマスタ」を作り、そちらに持たせるのが良いとされます。
この考え方は、「その8・テーブル設計 売上伝票」でも説明しています
件数が多くなる場合はこうした方が、データとして管理しやすいのですが、コードマスタのように件数が少なく、用途が限定されているテーブルについては、正規化するとプログラム(SQL)の手間が増えて効率が悪くなる事があります。
機械的に正規化のルールでテーブルを設計するのは、すべてにおいて適切であるとは言えないと思います。
効率よく開発できることも大切だと思います。
以上、今回でテーブル設計はすべて完了となりました。
次は、画面設計の予定です。
システム設計の話 その11・テーブル設計 商品マスタ [システム設計の話]
今回は、商品マスタテーブルの項目内容を考えていきます。
商品マスタは、商品に関する情報を登録します。
今回考えているシステムでは、帳票に印刷するときの商品名くらいがあれば事足ります。
項目名 | データ型 | 桁数 |
商品CD | 十進型 | 整数5桁 |
商品名 | テキスト型 | 全角20文字 |
商品名カナ | テキスト型 | 半角50文字 |
売単価 | 十進型 | 整数9桁 |
登録日 | 日付時刻型 | |
変更日 | 日付時刻型 | |
削除 | 十進型 | 整数1桁 |
特に用途はありませんが、カナ項目も付けました。実際のシステムだとカナ項目は、検索や並び替えで必要になってくるので必須です。今回は使いませんが。
また、今回は売上管理なので、商品の販売価格情報として、「売単価」項目を設けています。
ただし、実際の販売価格が、この項目で一律になるわけではありません。同じ商品であっても、値段は販売するその時その時の状況で変わることがよくあります。
そのため、値段については、売上伝票明細データに項目を設けて、そこに持つようにしています。
「売単価」項目は、デフォルトと言うか、標準価格的な参考値な項目となります。
今回の商品マスタは、いたってシンプルな構成としましたが、実際のシステムでは商品マスタの項目数が100を超えることもあります。
ここでもうちょっと商品マスタについて述べてみようかと思いましたが、業態によって扱う商品の特徴が異なるし、システムの目的でも必要な項目が異なってきます。
また、別の機会に掘り下げていきたいと思います。
システム設計の話 その10・テーブル設計 担当者マスタ [システム設計の話]
今回は担当者マスタです。
今回のシステムの用件としては、売上データがどの営業担当者かを識別するためだけなので、最小限の項目にしておこうと思います。
項目名 | データ型 | 桁数 |
担当者CD | 十進型 | 整数5桁 |
担当者名 | テキスト型 | 全角20文字 |
担当者名カナ | テキスト型 | 半角30文字 |
登録日 | 日付時刻型 | |
変更日 | 日付時刻型 | |
削除 | 十進型 | 整数1桁 |
最低限なので、コードと、担当者名、おまけでカナを付けました。
「担当者名」は、全角20文字。標準的な名前なら、漢字10文字もあれば収まるかと思いますが、余裕を取ってます。
「担当者名カナ」は、半角30文字。このくらいで大丈夫かなと。
「登録日」「変更日」「削除」は、他のテーブルと同じです。
以上、このシステムでは名前が表示できればそれで良いので、こんな感じです。
もうちょっと欲張ると、システムを利用するための、ログインID・パスワードとかを設けたり、所属部署とか入社年月日、自宅住所などの個人情報とか。
システムでは使わないけど、ここで管理したいから項目作ってよ、と言う要望もよくあります。
システム設計の話 その9・テーブル設計 顧客マスタ [システム設計の話]
顧客マスタの項目内容を考えて行きます。
お客さんに関する情報を入れておくマスタテーブルです。「顧客マスタ」以外に「取引先マスタ」、「得意先マスタ」とか言う呼び方もあるかと思います。
今回想定しているシステムでは、帳票に印刷するのに必要な、会社名や住所があれば充分なので、その最低限の情報にしておきます。
項目名 | データ型 | 桁数 |
顧客CD | 十進型 | 整数5桁 |
顧客会社名 | テキスト | 全角30文字 |
顧客会社名カナ | テキスト | 半角50文字 |
郵便番号 | テキスト | 半角10文字 |
住所1 | テキスト | 全角20文字 |
住所2 | テキスト | 全角20文字 |
住所3 | テキスト | 全角20文字 |
TEL | テキスト | 半角20文字 |
FAX | テキスト | 半角20文字 |
登録日 | 日付時刻型 | |
変更日 | 日付時刻型 | |
削除 | 十進型 | 整数1桁 |
伝票に印刷する会社名や住所・電話番号がメインです。
ACCESSのテキスト項目は、「フィールドサイズ」で指定した文字数分、全角半角の区別無く格納できます。なので、プログラムや設計で、全角のみ・半角のみとか制限しなくても、不具合は起こらないのですが、その他のデータベースだと違ってくることがあるので、混在させない方が無難と思われます。
まぁACCESSのみで作るくらいの規模なら、ざっくりでいいかも。
顧客会社名は、全角30文字。日経225企業の会社名で文字数を調べたら、最大23文字だったので、このくらいかなと。
顧客会社名カナは、半角50文字。半角英数カタカナで入力するようにします。
郵便番号は10文字とちょっと余裕を取ってます。3桁+ハイフン+4桁なので、8文字あれば足りるのですが。
間のハイフンをデータとして格納するかどうか、ちょっと悩むのですが、印刷・表示する側でいちいちハイフンを入れる手間を考えると、最初っからハイフン込みで格納しておいた方が楽です。
と、言うことで、郵便番号は、ハイフン込みで格納します。
住所は1から3までの3行構成で、各20文字まで。ちょっと余裕とりすぎかも。
こうしておけば、印刷されたときの見栄えを、入力の仕方で変えられるので。
TEL・FAXは、半角20文字。ちょっと長いかも。ハイフンや括弧を入れることもあるので、半角ならなんでも入力可にしておきます。こう言う所は、入力内容がシステムの不具合に繋がらないので、ざっくりです。
登録日と変更日は売上伝票と同じです。
削除は、その顧客を現在使用してよいかどうかのフラグです。
顧客との取引が無くなって、売上を入力しなくなるからと言って、いきなりその顧客のレコードを削除してしまうと、過去のデータを参照したときに、顧客名などが表示できない状態になってしまいます。
なので、「削除」の操作は、フラグを立てるだけにして、レコードは残しておくようにします。
入力画面等では、このフラグを見て、有効な顧客のみを選択出来るようにしておきます。
こうしておくことで、マスタの無い過去データができずに済みます。
実際にマスタレコードを消さないので、要らない情報がどんどん累積されるのでは、という心配もありますが、だいたいシステムは5年程度で見直しが入るので、その時にマスタの再整備も行われます。
新規登録と削除の件数がものすごく多い場合を除いて、特に心配はないと思われます。
どちらにせよ、データが残っている間は、マスタは消せない、っていうのがルールです。
以上、顧客マスタでした。
システム設計の話 その8・テーブル設計 売上伝票 [システム設計の話]
売上伝票データ・売上明細データテーブルの項目内容を決めていきます。
その前に、その7でも多少触れましたが、売上データは2つのテーブルに分けて管理します。なぜそのようにするのかについて、もう少し詳しく書いておきます。
顧客から注文があった、その内容を1つの売上内容として管理するとします。そして、1回の注文で複数の商品がされることを考慮します。
言葉で表現すると、「某日、A顧客から、A商品が1つ、B商品が5つ、C商品が3つ、注文があった」となります。
表で表現すると、
こんな感じでしょうか。
このイメージのまま、テーブルの項目を作ると、次のような状態になります。
明細の数だけレコードを作成するので、「注文番号」や「注文日」は、1つの注文に対して、1件あれば良いのですが、明細の行数分作成されてしまうことになります。
2つのテーブルに分けて管理するまでもない場合は、こういった構造のテーブルを作ることもありますが、だいだい件数の少ない小さなテーブルの場合です。売上データのような、件数が多くなるテーブルでは、無駄が大きくなります。
無駄な繰り返しを省くために、1つの注文に対して1件で良い部分と、複数件になる明細の部分を切り分けます。
明細側には、その明細がどの伝票のデータかわかるように、伝票番号を持たせておきます。
このような形でテーブルを作成することで、重複を省き効率の良いデータ管理が行えます。
では、売上伝票データテーブルの内容です。
盛り込みたい内容は、その4にあげているので、その内容から作成します。
項目名 | データ型 | 桁数 |
伝票番号 | 十進型 | 整数9桁 |
顧客CD | 十進型 | 整数5桁 |
注文日 | 日付時刻型 | |
納品日 | 日付時刻型 | |
担当者CD | 十進型 | 整数5桁 |
税抜合計 | 十進型 | 整数12桁 |
消費税額 | 十進型 | 整数12桁 |
総額合計 | 十進型 | 整数12桁 |
備考欄 | テキスト | 255 |
メモ | テキスト | 255 |
登録日 | 日付時刻型 | |
変更日 | 日付時刻型 |
キーは「伝票番号」です。
数値の項目は十進型で桁数を指定する形にしました。十進型を使うのはあまり一般的では無いような気もしますが、仕事で扱ったシステムは十進型が多かったです。
コード項目は桁数が重要になるので、そこを意識する意味で十進型にしてみました。
伝票番号は連番を持たせる項目です。ACCESSだと、とりあえず「オートナンバー」にしてしまう事が多いようですが、オートナンバーにしてしまうと、値を自由に設定できなくなるので、後々やっかいです。
基本的にオートナンバーの使用はお勧めしません。
注文日は、注文を受けた日付、納品日は注文を受けたときの納品予定日(だいたい)とします。実際には、納品が完了するまでを管理できるといいのですが、今回はそこまで考えません。
税抜合計・消費税額・総額合計は、それぞれ明細を積み上げた合計を入れておきます。こうしておくと、合計値のみを見たい場合に、いちいち明細から計算しなくて済むので。
備考欄は、帳票に印刷することのできる項目で、メモは印刷されない内部用のメモです。こういった要望は、よくありました。
登録日・変更日は、入力する項目ではなく、プログラムから設定します。登録日は新規登録を行った日時、変更日は変更された日時を設定します。
次ぎに売上伝票明細データです。
項目名 | データ型 | 桁数 |
伝票番号 | 十進型 | 整数9桁 |
明細番号 | 十進型 | 整数3桁 |
商品CD | 十進型 | 整数5桁 |
数量 | 十進型 | 整数9桁 |
単価 | 十進型 | 整数9桁 |
金額 | 十進型 | 整数12桁 |
消費税額 | 十進型 | 整数12桁 |
総額 | 十進型 | 整数12桁 |
備考 | テキスト型 | 255 |
変更日 | 日付時刻型 |
キーは、「伝票番号」「明細番号」です。
明細番号は、明細ごとの連番です。1伝票で同一商品を複数入力出来ないようにすれば、必要ないのですが、明細を入力した順番通りに印刷したい、と言う要望もあるかもしれないので、わかりやすくこうしています。
単価は明細行に記録するようにしています。単価について考えていくと、とっても深いものがあるので、あまり掘り下げませんが、常に商品マスタから参照する造りにしてしまうと、商品マスタの単価が変更になった際、入力時の金額と変わってしまいます。伝票の内容は入力時の単価で固定されるべきなので、明細に保持します。
金額には、単価×数量の値を入れておきます。参照するときに計算する手間が省けます。
同様に、消費税額、総額(金額+消費税額)にも、計算した値を入れておきます。
消費税額は、税額を計算し、端数を処理した整数を入れます。消費税の計上の仕方としては、伝票での合計額から消費税を計算する方法と、明細毎に消費税を出しておいて、それを積み上げて伝票の消費税とする方法があると思います。
前者は、簡単でわかりやすいです。
後者は、伝票の合計金額から計算した消費税額と、明細を積み上げた消費税額が一致しない場合があります。明細毎に端数を処理している為、このような事が起こります。
しかしながら、任意の明細の消費税を見ることができます。
前者の場合は、明細の消費税を見たいときに、不都合がでます。
上記を踏まえ、一般的には、明細毎に消費税を計算することが多いので、その方式にしてみようと思います。
ほとんどのスーパー・コンビニでも、明細ごとに消費税を計算していると思います。一度レシートをじっくり見てみてください。
備考は、明細毎の備考欄です。明細は印刷用のみにしました。
変更日は明細の登録・変更日時です。伝票みたいに登録日・変更日を別々に持たないのは・・・、持てないから。入力画面を作る際、持たない方が都合が良いので・・・。それはまたその時に説明します。
以上、売上伝票データ、売上明細データでした。