住所のDBテーブル、マスターの構成について

アクセスDB2013で、工事受注管理システムを作るところです。

現在、テーブルの仕様の作成をしております。

住所テーブルや、マスターを設計するところですが、工事受注時の工事する物件建物の住所登録や、協力会社登録などで登録する住所登録に使う住所の部分の設計でどのうようにしたら良いのか試行錯誤しています。

希望としては、データベースをクラウドに以降する事を想定して、データ容量を節約できるような設計にしようかと考えています。

例えば、

◆顧客テーブル
・顧客ID
・顧客名
・郵便番号
・住所1
・住所2
・番地
・建物名

などとするのと

◆顧客テーブル
・顧客ID
・顧客名
・郵便番号
・都道府県ID
・番地
・建物名

◆都道府県テーブル
・都道府県ID
・住所2

どちらが良いのか、または、その他の設計方法でもっと良いものがあれば、教えて頂きたいのですが。

よろしくお願いいたします。

No.5 ベストアンサー 回答者： yambejp 回答日時： 2014/10/06 10:17

＞この場合、一つのテーブルに同じ都道府県や市区町村が重複して入るのを良しとす

＞るお考えでよろしいでしょうか?
＞この場合、運用上のやりやすさを考えたご提案かと思いましたが、その場合、デー
＞タ量が多少大きくなるのはOKとされますか?

ENUMやSETで処理する限り、内部的には数値で管理されるため理論的に容量はさほど
大きくはなりません。したがって都道府県などリストの内容がほぼ普遍的なものに
ついては容量を理由に正規化する必要はありません。
（とはいえ10年単位で考えると都道府県合併も可能性はありますが・・・）

http://ja.wikipedia.org/wiki/%E5%88%97%E6%8C%99% …

また市区町村は結構変更があるのと都道府県の下につく二次データであることから
ENUMやSETに向きません。
正規化してもいいし、そのまま文字データで持ってもいいと思います。

＞「SQLにおける効率的なデータは必ずしも容量を少なくするものではありません。」

正規化というのは多数の重複がある前提の技術です。
重複数があまりない場合は理論的には効率化されていますが実際には無駄な処理に
なります。
また該当するカラムを集計のキーに使わない場合は無駄にインデックスを消費するため
データは大きくなる半面全体的なスピードは低下する傾向にあります。

たとえば仮に「東京都」でデータを抽出するとき、ダイレクトに書いてある場合は
直にインデックスが効きますが、正規化してあると都道府県テーブルから
「東京都」を探し、都道府県IDを抽出したうえで都道府県IDに紐づいたレコードを
探すことになるので、処理的にはむしろ効率的ではありません。

実際には都道府県IDを最初から抽出した状態から検索するインタフェースなので
逆に正規化しておかないと、都道府県リストを都度つくる無駄がでますので
善し悪しでしょう。

＞「都道府県名を入力欄にいれると不正にデータを抜かれやすくなると言われていた
＞時期もありますが、
＞セキュアな技術を使えばそれは避けられるでしょう」

端的にいえばSSLで解決します。
もちろん都道府県IDに紐づけばトラフィックも減らせるので悪いわけではありません。

またインターネットのトラフィックが爆発的に増えている現在
よほど重要な個人情報でないかぎり住所からデータを不正に抜くことが
現実的ではなくなっているきらいもあります。

＞都道府県名に対して、外部制約キーをつかったり、データ型をsetにしたりする
＞ことでデータを効率化することも可能です。

一部訂正します。
住所は都道府県が２つ以上存在しないためSET型よりENUM型がよいでしょう。

http://dev.mysql.com/doc/refman/5.1-olh/ja/enum. …

＞市町村名まで正規化するのが実用的かどうか

経験則でいえば、正規化する意義はあまり感じません。
例えば何万、何十万というデータ郡から市町村単位で広範なデータを集計する
ようなケースを想定しているのであれば話は別ですが、単なる住所録としては
メンテナンス性がおちるため市区町村名での正規化はお勧めしません。

この回答への補足

詳しいご説明をして頂き、ありがとうございます。

いくつかのご回答を比較・検討し、yambeさんのアドバイスを参考に以下のようにする方法を検討してしいます。

・顧客ID
・顧客名
・郵便番号
・都道府県　→ Enum型の数値を設定する。
・市区町村（住所１）
・住所２

そして、画面での処理は、
・Enum型により、テーブルの都道府県に数値を保存
・画面での入力は、ドロップボックスからの選択により、値は、Enum型と合わせる

とすれば、スッキリしするような気がします。

しかし、それを自分で実際にAccessでテーブルと帳票型のフォームを作って試したのですが、どうもうまく行きません。
テーブルの都道府県に同期したテキストボックスは表示できますが、それでは数値なので、都道府県名がわかりませんし、都道府県名のドロップボックスを作っても、そこで、数値から名前に変更するやり方がわかりません。

ここに関して、申し訳ありませんが、具体的なやり方を説明していただけると助かります。

よろしくお願いいたします。

補足日時：2014/10/07 13:35

この回答へのお礼

丁寧にご回答ありがとうございました。

ご説明の通り、都道府県はEnum型でデータをセットするか、または、ドロップボックスなどでリストに値を設定していれば、その値を住所録テーブルに数値として保存するやり方にしようかと考えました。

そうすれば、画面表示も自動で数値によりリストの項目（都道府県名）が表示されるのでよろしいかと思います。

いろいろ参考になる情報をありがとうございます。

お礼日時：2014/10/10 11:30

No.4 回答者： ki073 回答日時： 2014/10/04 20:38

No.1,3です。

現実的なやり方として、既に顧客情報が電子データ化されていて、今後追加はあまり発生しないのでしたら、既存データをそのまま使う（多分質問欄の最初のケース）のが良いと思います。
これから電子データを作成するのなら、郵便番号から住所に変換できるテーブルを作成しておき、自動的に住所（町域名まで）を入れるのがかなり効率的です。以前200件程度を一気に入れましたが1時間程度だっと思います。

入力効率を重視するか、容量を重視するかでデータの持ち方が変わってくると思います。
取引先は地域が偏っている場合が多いので、必要な都道府県だけのデータを使うことで郵便番号データはコンパクトになります。

郵便番号データテーブルを作るのは、住所入力の効率化なので、必要がないと言えばそうなのですが。

開発効率優先で、Ruby on Railsで1時間くらいでシステムを作って、住所データを入力したので、Accessとはずいぶん違うかもしれません。
No.3の方式を採用するのでしたら、
◆市町村名テーブル
・全国地方公共団体コード
・市町村名
・都道府県ID
としておいた方が、データベースの設計は楽かもしれません。Ruby on Railsの場合はスクリプトが簡単に書けますので、その時は情報が重複する都道府県IDは市町村名テーブルには入れませんでした。

ご参考に。

この回答へのお礼

ご回答ありがとうございます。

Rubyの場合には、スクリプト言語が簡単にかけるのですね。その場合のテーブルの作り方が若干変わってくるというのは面白いですね。私もRubyをネットで検索しましたが、初心者でも簡単に覚えられそうな点、魅力的です。

郵便番号をキーにした住所入力に関しては、私が作りたいシステムではあまり必要性がなさそうです。

知識としてはそういうやり方もあると言うことを頭に入れておきたいです。

ありがとうございました。

お礼日時：2014/10/07 12:24

No.3 回答者： ki073 回答日時： 2014/10/03 22:55

No.1です。

郵便番号データをリンクしておきます。
http://www.post.japanpost.jp/zipcode/download.html

このデータには全国地方公共団体コードが入っています。
5桁のうち上位2桁は都道府県を表しています。
◆市町村名テーブル
・全国地方公共団体コード
・市町村名
◆都道県名テーブル
・都道府県ID
・都道府県名
と2つに分けるとかなりコンパクトになります。
住所を入力するために、郵便番号のデータベースを使うのが便利なことも多いのですが、データを小さくするには上の2つのテーブルをもっておき、全国地方公共団体コードから都道府県と市町村名を特定する方法もあります。
・顧客ID
・顧客名
・郵便番号
・全国地方公共団体コード
・町域名
・番地
・建物名
とするとスッキりするかもしれません。

この回答への補足

ご回答ありがとうございます。リンクはとても参考になります。

全国地方公共団体コードの上位二桁を都道府県IDとして、都道府県テーブルに登録しておけば、簡単に都道府県と市町村名が、全国地方公共団体コードで取得可能ということがわかりました。

ご説明ありがとうこざいます。

補足日時：2014/10/04 12:58

この回答へのお礼

参考になる情報を教えていただきありがとうございました。

ただ、私の会社では、全国地方公共団体コードを使用したり、多くの住所を検索するような機械がありませんので、マッチした解決策ではありませんでしたが、情報としてはとても参考になりました。

今後ともよろしくお願いします。

お礼日時：2014/10/10 11:45

No.2 回答者： yambejp 回答日時： 2014/10/03 22:13

＞データ容量を節約できるような設計

容量については微妙ですね。
SQLにおける効率的なデータは必ずしも容量を少なくするものではありません。
また、参照しやすいデータ形式を構成（正規化）しても必ずしも高速化に
つながらないのがSQLの微妙なところですね。

・顧客ID
・顧客名
・郵便番号
・都道府県
・市区町村（住所１）
・住所２

入力のブレを防ぐためにはとするのが妥当です。
都道府県のカラムをわけておくと、大阪府大阪市など都道府県名と市名が
同じ場合、ユーザーがかってに省略してしまう場合があるので、
「必ず都道府県名からきちんとかく」という指示のため分けておく方が
運用しやすいです。

都道府県IDをつかうか本当の都道府県名を使うかはインターフェイスによります。
以前は都道府県名を入力欄にいれると不正にデータを抜かれやすくなると
言われていた時期もありますが、セキュアな技術を使えばそれは避けられるでしょう。

都道府県名に対して、外部制約キーをつかったり、データ型をsetにしたりすることで
データを効率化することも可能です。
もちろん都道府県IDを使った正規化も相応の効果はあると思いますので好き好きでしょう。

市区町村名については集計単位としてある程度まとめていた方が集計や抽出がらくに
なるため分けた方がなにかとお得です。
むしろSQLを使っている限りにおいて分けない手はないでしょう。
番地や建物名をわけても実際のところ集計に有利にはならないので、
住所２はごっちゃにしてもいいと思います。
ただあまり長い文字列だとvarcharで処理ができないので、想定として200文字程度で
収まるかどうかで判断するとよいと思います。

郵便番号と住所をリンクさせることは、便利といえばそうなのですが
必ずしも郵便番号と住所が一対一ではないこと、市町村の区分が変更になったり
郵便番号がかわったり、特定企業向けの郵便番号を記載したり・・・
など運用上のブレがかなりあるため、実際にはリンクさせないほうが現実的です。
（郵便番号自体を明記させることはやって損はないので必須項目でいいと思います）

この回答への補足

・顧客ID
・顧客名
・郵便番号
・都道府県
・市区町村（住所１）
・住所２

この場合、一つのテーブルに同じ都道府県や市区町村が重複して入るのを良しとするお考えでよろしいでしょうか?
この場合、運用上のやりやすさを考えたご提案かと思いましたが、その場合、データ量が多少大きくなるのはOKとされますか?

●「SQLにおける効率的なデータは必ずしも容量を少なくするものではありません。」という部分、もう少しその理由をご説明してくださると助かります。

●「都道府県名を入力欄にいれると不正にデータを抜かれやすくなると言われていた時期もありますが、セキュアな技術を使えばそれは避けられるでしょう」

↑ここについては、どういったセキュアな技術がアクセスで可能なのでしょうか?

●都道府県名に対して、外部制約キーをつかったり、データ型をsetにしたりすることでデータを効率化することも可能です。
↑
データ型をSETにするとは、どんなやり方なのでしょうか?

●郵便番号と住所をリンクさせることは、便利といえばそうなのですが必ずしも郵便番号と住所が一対一ではないこと、市町村の区分が変更になったり郵便番号がかわったり、特定企業向けの郵便番号を記載したり・・・など運用上のブレがかなりあるため、実際にはリンクさせないほうが現実的です。
↑
この部分は、私の考えと似ているかも知れませんが、市町村名を郵便番号でキーとしたテーブルを作って、そこからデータを取得したとして、そのデータ数があまりにも多くなり、その後、市町村名が代わったり、郵便番号が代わったりしたとき、管理が大変だなという考えもあり、今一つ、市町村名まで正規化するのが良いのかどうかという迷いがありました。

また、弊社の協力会社や物件の住所というのは、数が限られていますから、それを正規化してまでメリットがあるのかというとそうでもなさそう。もちろん、データ数はある程度節約できるかもしれませんが。

いっその事、
・顧客ID
・顧客名
・郵便番号
・都道府県
・市区町村（住所１）
・住所２

この構成で、都道府県のところだけ、ID化して、外部キーで管理するというのもいいかもしれないと、今考えております。
ただ、これで市区町村名を入力していくと、同じ文字列が重複する事は多々あると思います。その場合、クラウド化した時には使用されるデータ容量が計算されますので、正規化したほがいいのかな?と、考えてしまいます。

市町村名まで正規化するのが実用的かどうか、その辺のところも教えていただけると助かります。

よろしくお願いします。

補足日時：2014/10/04 13:25

この回答へのお礼

詳しい説明をしていただきありがとうございました。

正規化しても必ずしも処理速度をあげるわけではないというところが、テーブル仕様を考える点でとても参考になりました。

データがあまりにも重複しそうなところ以外は、正規化セずに、数値で登録するようにしようと思います。

よろしくお願いします。

お礼日時：2014/10/10 11:42

No.1 回答者： ki073 回答日時： 2014/10/03 17:20

最初の方は、住所1、住所２とせずに、郵便番号簿と同じ区分（都道府県名、市区町村名、町域名）にしておいた方が、郵便番号から自動入力するときに便利です。

二番目の方は郵便番号から住所が分かるので省略できるということでしょうか？？

実際にやってみれば分かりますが、複数の町域名が同じ郵便番号になってることが結構あります。また札幌市中央区の場合のように
北五条西（１～２４丁目)
北五条西（２５～２９丁目)
番地で郵便番号が違う所も結構あります。
郵便番号から町域名を導きだすには無理な住所もありますので町域名の省略は難しいです。
・顧客ID
・顧客名
・郵便番号
・町域名
・番地
・建物名
とすると、郵便番号データベースを持っている場合には住所が確定します。

少し前に実際に住所を入力するのに作ったものは
・郵便番号
・町域名修正
・番地
・建物名
としました。郵便番号から町域名が特定きない場合だけに「町域名修正」に書き加えるというものです。こちらの方がデータ量はかなり少なくなりますが、プログラムはちょっと複雑になります。

それと、郵便番号が変更にあることがごく稀にありますから、注意が必要な場合もあります。

この回答への補足

ご回答ありがとうございます。
・顧客ID
・顧客名
・郵便番号
・町域名
・番地
・建物名
こちらの方法でする場合、郵便番号に紐づく別のテーブルでは、
恐らく、都道府県名、市区町村名をID番号などに置き換えて、
これら2つの情報をマスターなどで管理するというやり方でよろしいですか?

{郵便番号テーブル}
・郵便番号NO
・都道府県ID
・市区町村ID

{都道府県マスター}
・都道府県ID
・都道府県名

{市区町村名マスター}
・市区町村ID
・市区町村名

よろしくお願いいたします。

補足日時：2014/10/03 22:05

この回答へのお礼

ご回答ありがとうございました。
郵便番号での検索の仕組みが良くわかりました。機械があれば、利用してみたいと思います。

よろしくお願いします。

お礼日時：2014/10/10 11:34