- PR -

テーブルの設計（年度列の追加）

1|2 次のページへ»
投稿者投稿内容
ふらわー
常連さん
会議室デビュー日: 2008/01/11
投稿数: 33
投稿日時: 2008-08-11 15:46
VB.NETとSQLServerを使って開発しております。

既存のテーブルに年度の列を追加して、レコードを取得したいと考えています。
どのようにしてテーブルの設計をすれば一番スマートか、教えていただきたいです。

Aのマスターテーブルはこのようになっています。
年度の列は例としてここで追加しいるだけで、まだ列はありません。

【コード】　【名称】　【金額】　【年度】
　　A　　 　　A階層　 　20000 2003
A A階層 15000 2008
B B階層 40000 2003
B B階層 30000 2008

レコードは1年度ごとにあるわけではなく、何年かごとに1レコードです。
2007年度までは、コードAの金額は20000円（1行目のデータを使用）で、2008年度以降は15000円（2行目のデータを使用）となります。
2007年度までは、コードBの金額は40000円で、2008年度以降は30000円となります。

今の所2003年度までと2008年度以降のデータしかないのですが、今後増えていく可能性があります。

自分ではこの方法を考えました。
①開始年度と終了年度をAテーブルに入れる。
②年度マスターテーブル作成⇒あらかじめ年度を取得⇒Aテーブルに年度列を追加。

しかし①の方法だと開始年度と終了年度が常にわかっていないとダメです。
終了年度については開始年度が決定した時点ではわかっていないので、毎年調整しないといけなくなるので面倒な気がしました。
②だとテーブルを作成するのと、SQLをもうひとつ発行しないといけないのが面倒だと思います。

他にスマートな方法はありませんでしょうか？

是非とも教えてください。
よろしくお願いいたします。
七味唐辛子
ぬし
会議室デビュー日: 2001/12/25
投稿数: 660
投稿日時: 2008-08-11 16:01
終了年度が常にわかっていないとダメです。 というのがいまいち不明
わかっていないならＮＵＬＬを入れるというのはだめなのでしょ
ひろれい
ぬし
会議室デビュー日: 2006/03/02
投稿数: 486
お住まい・勤務地: 万博開催地
投稿日時: 2008-08-11 16:07
アプリから何がしたいために年度という列を追加するのでしょうか？

同一年で２度以上金額が改定されることは運用上ありえないのかな、と思ってみたり(^_^;)
ふらわー
常連さん
会議室デビュー日: 2008/01/11
投稿数: 33
投稿日時: 2008-08-11 16:41
>七味唐辛子様

ちょっと書き方がおかしかったかもしれないです。すいません。
主キーを設定するのにNULLはまずいなと思ったので･･･
このデータちょっとややこしいです。
主キーは今コードです。

【コード】　【名称】　【金額】　【年度】
A A階層 20000 2003
A A階層 15000 2008
B B階層 40000 2003
B B階層 30000 2008

この1行目と3行目の示すデータは2003年度に改定されたものだという意味です。
同じく2行目と4行目は2008年度に改定されたものだという意味です。
2008年度のデータが入るのは来月からで、改定されるタイミングは今後もわかりません。
元々そういう思惑で作っていなかったものらしく（私の会社で作ったものではないので詳細不明ですが･･･）年度の考慮が全く入れられてなかったそうです。
後々年度ごとに変わるデータだということが判明し、今修正が必要となっています。

もしこのテーブルに開始年度と終了年度を入れるとすれば

【コード】　【名称】　【金額】　【開始年度】 【終了年度】
A A階層 20000 2003 2007
A A階層 15000 2008 NULL
B B階層 40000 2003 2007
B B階層 30000 2008 NULL

こうなります。
主キーはコードと開始年度に設定すればいいのですかね？？
もし開始年度がNULLの場合はダミーで年度を入れるということで回避となるのでしょうか？

>ひろれい様

ちょっと言葉足らずでした。すいません。
元々何年度かごとに変わるデータだったはずなのに、それを考慮に入れずテーブル設計していた人のミスのようです。
というわけで例えば2005年度のコードAであれば金額は20000円となり、2008年度のコードAであれば15000円ということになります。
同一年で2度以上金額が改定されることはありません。
予定では3年に一回位改定が行われるデータみたいです。
rain
ぬし
会議室デビュー日: 2006/10/19
投稿数: 549
投稿日時: 2008-08-11 16:53
あんまし考察していませんが、２つほど考えてみました。

③単純に各年度ごとにデータを持つ

メリット：検索が楽かつ高速
デメリット：年度ごとにデータ作成が必要、過去データの修正が面倒そう

④価格を改定した年度のレコードだけ持って、あとはクエリでなんとかする
（ある年度Xのデータがほしければ、Xを超えない中で最大の年度を持つレコードを引っ張ればOK）

メリット：データのメンテナンス（移行や更新）が楽
デメリット：検索速度が多少落ちる
unibon
ぬし
会議室デビュー日: 2002/08/22
投稿数: 1532
お住まい・勤務地: 美人谷　　　　　　　　良回答(20pt)
投稿日時: 2008-08-11 18:22
引用:

ふらわーさんの書き込み (2008-08-11 15:46) より:
年度の列は例としてここで追加しいるだけで、まだ列はありません。

【コード】　【名称】　【金額】　【年度】
　　A　　 　　A階層　 　20000 2003
A A階層 15000 2008
B B階層 40000 2003
B B階層 30000 2008

好みの問題もあり、どう作ってもそれほど大きくは違いはないとも思います。

まず、提示された例について述べますと、たとえば2004年度の金額を求めたい場合、2003年度のレコードの存在と2008年度のレコードの存在の2つを見つけて、さらには2004年度も2005年度も2006年度も存在しないことを確認して、やっと2003年度のレコードのほうから引っ張ってくればよいと判明するようなつくりは使いにくいと思います。場合によっては自己結合が必要になることもあります。冗長さはないは良いことなのですが、リレーショナルDBとしては使いにくい設計です。

これを解決するには、開始年度と終了年度の列を持つか、あるいは1年度単位でレコードを持つかでしょう。
ただ、どちらにしても未来の扱いが面倒になります。特に終了年度を持たせると NULL を入れると検索が面倒くさいです。MAX という意味で 9999 などを入れるのもひとつのやりかたでしょうが、将来、1万年問題で悩まされます。int の最大値などの値 2147483647 などを入れると運用時に「なんだこの値？」となりかねません。
1年度単位だと、何年まで未来の年度を入れておかないといけないのかで悩みます。運用上、ためしに10年後をシミュレートしたい、などということがもしかしたらあるかもしれません。

理論的にきれいなのは、開始年度と終了年度を付けて、終了年度は NULL を入れることなんでしょうけど、泥臭い実運用を考えるとちょっと悩みます。
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2008-08-12 01:07
引用:

同一年で2度以上金額が改定されることはありません。

ということであれば、当初提示された
引用:

【コード】　【名称】　【金額】　【年度】
　　A　　 　　A階層　 　20000 2003
A A階層 15000 2008
B B階層 40000 2003
B B階層 30000 2008

でいい様に思えます。（年度は開始年度を入れる）

抽出する時は
コード:
select M1.* from 金額テーブル M1
join (
    select M2.コード, max(M2.年度) as 年度 from 金額テーブル M2
    where
        M2.コード = :入力値：コード
        and M2.年度 <= :入力値：年度
) V
on M1.コード = V.コード
and M1.年度 = V.年度

ってな感じ？

データを厳密にするのであれば、コードと年度の組でユニークキー( or 主キー）を付ければオッケイ。

いろいろな箇所からフェッチされるのであれば、テーブルを返すストアドファンクションにするのもありかと。

もし前提である
引用:

同一年で2度以上金額が改定されることはありません。

が崩れるのであれば、開始年度ではなく開始日付を持った方がいいでしょうね。

ふらわー
常連さん
会議室デビュー日: 2008/01/11
投稿数: 33
投稿日時: 2008-08-29 18:20
皆様

返答が遅くなりまして申し訳ありません。
皆様ご回答ありがとうございました。

結局開始年度列だけ追加することになってしまいました。
（終了年度列を入れると運用面で面倒だということで、お客さんからＮＧが出ました...）

コードと開始年度でプライマリーキーを設定して、SQLでなんとかしようとしているのですがうまくいきません。。。
皆様のお力をもう少し貸してください。

かずくんさんから提示いただいたSQL文を少し修正してみて以下のように書いてみました。

select M1.コード,M1.名称,M1.年度,M1.金額 from 金額テーブル M1
join (
select M2.コード, max(M2.年度) as 年度 from 金額テーブル M2
where
M2.コード = @入力コード
and M2.年度 <= @入力年度
　　　　group by M2.コード,M2.名称,M2.年度,M2.金額
) V
on M1.コード = V.コード
and M1.年度 = V.年度
group by M1.コード,M1.名称,M1.年度,M1.金額

しかしきちんとデータを取ってきません。

例えば、コードAで2008年度とした場合に2008年度だけの列を取ってきてほしいのに、2003年度の列も同時に取ってきてしまいます。

どのようにすればこのレイアウトのテーブルから、2003年～2007年までは2003年度の列のみ取得、2008年以降は2008年度のみの列を取得するようにできるのでしょうか？

お手数をおかけしますが、ぜひお力をお貸しください。
宜しくお願い致します。
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）