1:nなデータを検索しやすくDBに入れたい (2008-03-20)

いま後輩が悩んでいるネタ。たとえば、以下のようなデータをデータベースにどう格納するかを考える。

夜行バスと割引
名前	行き先	片道運賃	割引
セレナーデ号	広島駅	8400	早売14、ネット割2%、往復割引
出雲・松江ドリーム名古屋号	出雲市駅	9000	早売21、ネット割2%、往復割引
オリーブ松山号	JR松山支店	10000	往復割引

手元に資料があるもので適当に問題を置き換えたので例が悪いが、とにかくこういうデータをどういうスキーマで格納するかを考える。教科書どおりに沿って考えると、このデータは正規化されていないので、正規形に変える必要があるだろう。

夜行バス
ID	名前	行き先	片道運賃
1	セレナーデ号	広島駅	8400
2	出雲・松江ドリーム名古屋号	出雲市駅	9000
3	オリーブ松山号	JR松山支店	10000

割引
ID	割引
1	早売14
1	ネット割2%
1	往復割引
2	早売21
2	ネット割2%
2	往復割引
3	往復割引

このままデータベースに突っ込めば、「ネット割2%」が適用されるバスの情報が欲しければ以下のSQL文で参照できるはず:

SELECT 名前 FROM 夜行バス LEFT JOIN 割引 USING(id) WHERE 割引='ネット割2%';

問題はここからで、このスキームに従うと、「早売14とネット割2%の両方が指定されている夜行バス」の一覧が欲しいときに割引テーブルを自己結合するか、早売14が指定されているバスの集合とネット割2%が指定されているバスの集合を求めて積集合を取るかしないといけなくなる。指定が3つ4つと増えていくと計算量がみるみる増えていくので、あまり現実的な話ではない。

MySQLにはSET型というものがあるので、これからもずっとMySQLで運用する気なら、割引カラムをSET型で宣言すればよい。

CREATE TABLE 夜行バス (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  名前 VARCHAR(20) NOT NULL,
  行き先 VARCHAR(15) NOT NULL,
  片道運賃 SMALL INT UNSIGNED NOT NULL
  割引 SET('早売14','早売21','ネット割2%','往復割引')
);

「早売14とネット割2%の両方が指定されている夜行バス」は以下のSQL文で求められるはず:

SELECT 名前 FROM 夜行バス WHERE FIND_IN_SET('早売14',割引) AND FIND_IN_SET('ネット割2%',割引);

ただ、どうせMySQLでいくなら、割引カラムにFulltextインデックスをつけてmatch() against() したほうが楽だったりして。

問題はほかにもあって、なんか元データはエクセルに以下のような感じで収まっているのだという:

名前	行き先	片道運賃	早売14	早売21	ネット割2%	往復割引
セレナーデ号	広島駅	8400	○		○	○
出雲・松江ドリーム名古屋号	出雲市駅	9000		○	○	○
オリーブ松山号	JR松山支店	10000				○

確かにそれは面倒だ。強まった人なら1発でいい感じに整形できるのだろうけど、私だったら正規表現の使えるエディタにコピペして

s/○$/往復割引/g
s/○(\\t[^\\t]*)$/ネット割2%\\1/g
s/○(\\t[^\\t]*)(\\t[^\\t]*)$/早売21\\1\\2/g
s/○(\\t[^\\t]*)(\\t[^\\t]*)(\\t[^\\t]*)$/早売14\\1\\2\\3/g

と置換するかなあ。置換すべき項目がもっと多いならもう少しエレガントな方法を考えるけど、10個くらいだったら上のようなやり方でやっちゃったほうが早そう。

set型か。覚えておこう。あとどうでもいいが、早「売」14。→http://www.kousokubus.net/PC/BPGD/BPGD221_01.htm というか、実在のデータじゃないのね。

これはやってしまいましたね。修正しました。ご指摘ありがとうございました。あとデータはJR東海バスの『JRハイウェイバス時刻表2008.3.1→6.30』の26ページから引用しました。