MySQL 住所のDB設計について

Question

PHPを利用したWebシステムの開発をしています。
住所情報を「郵便番号,都道府県,市区町村,番地,建物名」の計5つでカラムを分けて保存しています。
そのため、一つのテーブル内で異なる役割の住所が複数あるとカラム数が増大してしまうため住所テーブルを作成して分離することを検討しています。

このとき、以下のように各テーブルの住所を一つのテーブルに統合して管理する事は問題になるでしょうか？
ご意見を頂きたいです。
よろしくお願いいたします。

※各テーブル名、カラム名は例です。
※実際はもっと多くのテーブル、住所データが存在します。
※住所自体のカラムを統合する事は現状考えていません。

■企業テーブル ( company )
・company_id
・company_name
・company_address_key（ address.address_id ）

■従業員テーブル（ employee ）
・employee_id
・employee_name
・employee_address_key（ address.address_id ）

■住所テーブル（ address ）
・address_id
・postcode
・prefecture
・city
・block
・building

Answer 1

一つのテーブル内で異なる役割の住所が複数あるとカラム数が増大してしまうため住所テーブルを作成して分離する

各テーブルの住所を一つのテーブルに統合して管理する

明確な正解がある問題ではないので、どちらかと言えば意見交換向きのテーマかな、と思いますが……。

※実際はもっと多くのテーブル、住所データが存在します。

基本的には「1件の個人/組織に対して複数件の住所情報を紐づけられること」または「（個人や組織とは無関係に）土地や住居そのものの情報を主体として管理すること」が要件として求められるかどうかによるでしょう。

前者のケースについては、概ね次のようなパターンに大別できます。

1. 住所の属性（戸籍上の住所/実際の居住地/実家、保証人などの連絡先/配達物の送り先/勤務先など）ごとにレコードを作成する。

2. 住所情報の新規登録または変更が行われる都度、新規レコードを追加する（住所変更の履歴を残す）。

PHPを利用したWebシステムの開発をしています。

例示されたテーブルの構成について、強いて問題点（というより課題点）を挙げるとすれば、

• テーブル間での[address_id]の紐づけが誤った形で行われてしまう可能性がゼロではない。

• 企業情報または従業員情報の新規登録/変更を行う際、新しい[address_id]の競合を確実に回避するための工夫が必要となる。

といったところでしょうか。

Answer 2

一つのテーブル内で異なる役割の住所が複数あるとカラム数が増大してしまう

例示のテーブル群ではその様な事が起きないのでは？

各テーブルの住所を一つのテーブルに統合して管理する事は問題になるでしょうか？

その事自体が問題にはならないでしょうが、別テーブルにする意味が無いと思います。

Answer 3

既出の回答の通りだと思います。
ちらっと思いついた事例を挙げると
・同じ企業の同じ支店(営業所)でも部署や人によって微妙に住所(番地や階数/部屋番号)が違う。
・完全に同じ場所を示すが、表記にこだわりのあるユーザがいる。
(「1丁目1番地1号」と書いたり「1-1-1」と書いたり。)
みたいなことも考慮する必要があるなら、住所だけ外に出すと都合が悪いかもしれませんね。
(で、これらを別々に扱うなら本末転倒になる、と…。)

Answer 4

住所は基本的に個別IDに対してユニークに設定されるため再利用されないので正規化する必要性は低いと思います
ただ、フォーマットを合わせたいということであれば住所専用のテーブルを設けても構いません