MYSQLデータベースの仕様変更の問題

質問

データーベースのテーブルで
都道府県を記録するカラムが一つだけあり
１レコードにつき、一つだけ、長崎、東京、青森、などと
都道府県を記録できるになっています。
が、今回、１レコードにつき１つだけという仕様を改め
長崎と大阪、京都と大阪と北海道、等と、
１レコードにつき、最大４７都道府県の地域データを記録できるように
したいと考えました。
そうした時、カラムを４７つに増やさなければならないのでしょうか？
一般的に、このような時データベースはどのように組むのが一般的なんでしょうか？

回答No.3

chukenkenkou · ベストアンサー

#2回答者です。

表のデータ例、得たい結果例などを示してもらえると、より具体的な回答ができるのですが。。。

都道府県名（？）が入る表の検索時、結果は1行のみの返却でないと、何か不都合があるのですか？例えば、都道府県が3件あった場合、検索結果が3行になると、不都合があるのでしょうか？

（親id,親データ,都道府県1,都道府県2,都道府県3）といった1行で得られないと、不都合はありますか？

（親id,親データ,親毎の子通番,都道府県1)
（親id,親データ,親毎の子通番,都道府県2)
（親id,親データ,親毎の子通番,都道府県3)
といった3行で得る方法では、不都合があるのでしょうか？

>このような時データベースはどのように組むのが一般的なんでしょうか？

検索条件の指定方法等が不明ですが、47都道府県用に47列を持つというのは、一般的ではないです。逆に1行で1都道府県という現状の方が、より一般的な使い方です。

MySQLのバージョンが不明ですが、SQLの例を示します。MySQL 4.1以降なら、動くと思います。

＜表定義例＞
create table 親表
(親id int primary key auto_increment,
data1 varchar(30),
data2 int);
create table 子表
(親id int,
子通番 int auto_increment,
都道府県 varchar(4),
primary key(親id,子通番));

＜テスト用データ＞
insert into 親表 values
(null,'a',1),
(null,'b',2),
(null,'c',3),
(null,'aa',1),
(null,'bb',2),
(null,'cc',3),
(null,'dd',4);

insert into 子表 values
(1,null,'北海道'),
(2,null,'北海道'),
(2,null,'沖縄県'),
(3,null,'沖縄県'),
(3,null,'北海道'),
(3,null,'東京都'),
(4,null,'北海道'),
(5,null,'北海道'),
(5,null,'沖縄県'),
(6,null,'沖縄県'),
(6,null,'北海道'),
(6,null,'東京都'),
(7,null,'沖縄県'),
(7,null,'北海道'),
(7,null,'東京都'),
(7,null,'神奈川県');

＜検索例＞
-- 全件検索
select o.親id,data1,data2,都道府県
from 親表 as o,子表 as m
where o.親id=m.親id
order by o.親id,子通番
;

-- 条件中のいずれかの都道府県があれば検索
select o.親id,data1,data2,都道府県
from 親表 as o,子表 as m
where o.親id=m.親id
and 都道府県 in('沖縄県','北海道','東京都')
order by o.親id,子通番
;

-- 条件中のすべての都道府県が含まれていれば検索
select *
from 親表
where 親id in(
select o.親id
from 親表 as o,子表 as m
where o.親id=m.親id
and 都道府県 in('沖縄県','北海道','東京都')
group by o.親id
having count(distinct 都道府県)=3)
;

お礼

とても参考になる回答有難うございます。
最終的に親、子のリレーショナル関係は
chukenkenkouさんの作成例のような感じで作成しました。

検索例もつけて頂いて有難うございます。

回答No.4

h_0

カラムのタイプをSETにするのはだめですか？
SETだと、
　1. ひとつのカラムですむ
　2. 最大６４都道府県まで登録できる
　3. 2バイト程度しか領域を取らない
　4. 文字列方のカラムより検索が速い
などというメリットがあります。

CREATE TABLE address (pref SET('東京都', '埼玉県', ...));
なんて感じでどうでしょうか？

勘違いしてたらごめんなさい。

回答No.2

chukenkenkou

>１レコードにつき、最大４７都道府県の地域データを記録

そう考えた理由は、何でしょうか？
現在、正規化されている（？）ものを、わざわざ正規化されていない状態にするのですか？

>カラムを４７つに増やさなければならないのでしょうか？

配列やユーザ定義型を実装しているRDBMSもありますが、MySQLは未実装ですから、わざわざ扱いにくい表にしたいなら、ご自由に。

この表に対してどういう操作を行なうのか知りませんが、「1個の列にカンマ区切りで」なんてことをすると、SQL側で操作しにくい表になってしまい、アプリケーション側での処理も煩雑になりますよ。
また、もしtext型を考えているなら、text型は無闇に使用すべきではありません。インデクスを定義できるようになっていますが、varchar等に比べ、インデクスを有効利用できません。

>プログラム側で制御させないようにするとすると、
結局はカラムを多く作らなければならないんですよね・・・。

これは、具体的にどういうケースの話ですか？

お礼

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

＞そう考えた理由は、何でしょうか？

複数のデータを入れれるように仕様変更したのはなぜですか？ということですか？
例えば、いくつかあるレコードのうち、
あるレコードが大阪府と、和歌山に関係するレコードだった時、
カラムが一つだけだと、大阪府か、和歌山かどちらか一つしか入れれなく
なります。
もし、もっと複数定義させたいということになると、
欲を言えば、４７都道府県すべてを自由に定義できるようにすると
最大で４７つカラムが必要になってきます。

実際は１０コ程度でこと足りるんでしょうが・・・。

＞わざわざ扱いにくい表にしたいなら、ご自由に。

ここで、質問したのは、このやり方だと、扱いにくい表になるのではないか、
こういう時、どうするのが一般的なのか？ということで質問しました。

＞これは、具体的にどういうケースの話ですか？

カンマ区切り等をしたものを、プログラム側で制御するのではなく
親テーブルのidと子テーブルのidをリレーションさせて、
子テーブルのカラムには複数の地域情報をいれれるようにするため
子テーブルに多くのカラムを作ろうとするケースです。

回答No.1

めとろいと（@naktak）

親テーブル　id(primary), 他カラム
子テーブル　id(primary), 都道府県(primary)

としてもいいし、今までのカラムの型がテキストなら、ある法則に従って
文字列として登録してもいいと思います。
（カンマ区切りに都道府県が登録される、など）
その中でカラム数を増やすというのも手です。
（47つ登録出来るという概念にしたり、カラム毎にある都道府県だと定め、
フラグなどを持たせるようにするなど）

但し、抽出や登録を行う上での制御を考えたら、親子関係にした方が
いいと思います。
特定の都道府県に大して何かしたい場合などは、親子関係でないと
プログラム側で制御を行う必要が出てくる可能性があるからです。
プログラム側で制御を行わないと抽出出来ないなどといったテーブルレイアウトは
SQLの利便性を損ねる為、間違いだと思います。
また、テーブルがグリッドである事を無視して特殊な設計を行うと、
仕様変更が発生した場合に対応が難しく再度揉める可能性もあります。

そういう設計を、現役を離脱している最高権限者から強いられて力技的な
プログラムを組んだ記憶が・・・。

システム上、そのテーブルをどう操作するのか分からない為、利用用途を
考えた上で決定する必要があります。
現在、カラム数が1つのようですし。

お礼

回答ありがとうございます。
最初親テーブルに４７カラム追加しようとしていましたが、
回答を参考にして
親のＩＤにリレーションさせて、子テーブルに
都道府県データを記録することにします。
でも、プログラム側で制御させないようにするとすると、
結局はカラムを多く作らなければならないんですよね・・・。