txqz memo

1:nなデータを検索しやすくDBに入れたい

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

夜行バスと割引
名前行き先片道運賃割引
セレナーデ号広島駅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ページから引用しました。