時給の変更に対応する方法

質問

access2000を使って従業員の人件費を計算しようと思っています。
時給テーブルで従業員番号、従業員名、時給
日時テーブルで日付、労働時間、従業員番号
これで人件費が計算しようと思ったのですが、よく考えたら時給は変更になることがあります。
時給テーブルの時給を変更してしまうと過去の人件費まで変わってしまうので、このままではダメなことが分かりました。
かといって毎日各従業員ごとに時給を入力するのは間違えのもとになると思うので避けたいと思っています。
時給の変更に対応できるようにするにはどのようにしたらいいのでしょうか。
ご存知の方、よろしくお願いします。

回答No.9

agricap · ベストアンサー

SELECT SUM(A.労働時間*B.時給) FROM 日時テーブル AS A, 時給テーブル AS B
WHERE A.従業員番号=1 AND B.従業員番号=1
　　AND B.開始日<=A.日付 AND A.日付<=B.終了日
　　AND '2006/01/01'<=A.日付 AND A.日付<='2006/03/31'

>この場合、ある特定の日の人件費は大丈夫だと思うのですが、何日～何日の人件費を求めようとした時に、もしその期間内で時給が変わっていても問題ないでしょうか。
そもそもこの場合の日付を指定した人件費はどのように求めたらいいのでしょうか。

問題ないと思いますが、何が心配ですか？
たとえば、従業員１の2006/1/1～2006/03/31までの給料は、以下
のようなＳＱＬで求められます。
（日付の書き方はシステムによって変わりますので、一例です）

SELECT SUM(A.労働時間*B.時給) FROM 日時テーブル AS A, 時給テーブル AS B
WHERE A.従業員番号=1 AND B.従業員番号=1
　　AND B.開始日<=A.日付 AND A.日付<=B.終了日
　　AND TO_DATE('2006/01/01')<=A.日付 AND A.日付<=TO_DATE('2006/03/31')

こういうことをしたい場合、性能は日時テーブルに時給をもった方
が安全ですが、多分この程度のＳＱＬなら、性能も大抵は問題ありません。
日時デーブルの従業員番号、日付、時給テーブルの従業員番号ぐらいにインデッ
クスをつけておけば万全でしょう。
従業員が数万人ぐらいいても、１秒以内に終わるレベルだと思います。

お礼

ようやくできました。ありがとうございました。

回答No.8

agricap

＃７です。念のため、補足しておきます。
時給テーブルには、１従業員につき、複数の時給の情報を持たせる
ことができます。たとえば、従業員１の2006/1/1～2006/1/31 は８００円、2006/2/1～2006/3/31は９００円であったとすると、

従業員番号、　　開始日、　 終了日、時給
　　　　１、2006/01/01、2006/01/31、 800
　　　　１、2006/02/01、2006/03/31、 900

のような感じになります。

補足

この場合、ある特定の日の人件費は大丈夫だと思うのですが、何日～何日の人件費を求めようとした時に、もしその期間内で時給が変わっていても問題ないでしょうか。
そもそもこの場合の日付を指定した人件費はどのように求めたらいいのでしょうか。
分かりにくい日本語ですみません。

回答No.7

agricap

＃３です。
以下のような感じです。時給テーブルを２つに分けます。
時給テーブル（従業員番号、開始日、終了日、時給）
従業員名テーブル（従業員番号、従業員名）

少し専門的にいうと、
開始日と終了日を加えたとき、
時給テーブル（従業員番号、従業員名、開始日、終了日、時給）
というふうに１つにしてしまうと、
第２正規形でなくなって、よろしくありません。
具体的には、同じ従業員番号に対して従業員名を複数レコード
に入力するため、間違いやすいですし、従業員名が誤っている
ことに気づいた場合、複数レコードを修正しなければならない
ので、間違いやすい、といえます。

回答No.6

noname#22222

s_husky です。

「マルチ時給の場合はテーブル構造の再検討が必要では？」と感じましたので再投稿します。

私なら、従業員に複数の時給を設定する場合、次のようなテーブル構造にします。

従業員テーブル:ID,従業員番号,従業員名,現行時給ID
時給テーブル:ID,時給額,登録日（又は、適用開始日、適用終了日）

さて、この場合、＜日時テーブル＞には、最終的には＜時給テーブル.ID＞でリンクすることになります。
＜従業員テーブル.現行時給ID＞は可変だからです。
なお、この設計は、＜時給が不可逆的に増加する＞という前提が崩れると予測しています。
＜開始日＞＜終了日＞でなく＜登録日＞としている理由です。

※列＜時給テーブル.適用従業員ID＞が、必要か否かも検討すべきかと思います。
※５つ程度の時給を３０人に適用している場合、５レコードで管理するのか150レコードを準備するのかは検討に値します。

回答No.5

O_cyan

>時給テーブルの時給を変更してしまうと過去の人件費まで変わってしまうので、このままではダメなことが分かりました。
>時給の変更に対応できるようにするにはどのようにしたらいいのでしょうか。

リレーションで１対多で時給テーブルと日時テーブルを結合していると思いますが参照整合性をとっていてもフィールドの連鎖更新のチェックを外せば時給の金額が変更になっても時給テーブルの過去の時給の金額は変更した金額に更新されません。
単純にリレーションシップのフィールドの連鎖更新のチェックを外すだけで済みます。

回答No.4

noname#182251

給与は何回も計算し直したり、過去に遡って計算するような性格のデータではないと考えます。
従って計算した結果は
・０６年０６月給与
・０６年０６月２５日給与
など最低
・日付型
・金銭型
・個人ID
フィールドを持ったテーブルに記録すれば良いでしょう。乱暴ないい方をすれば、こうすることにより「時給」データは最新のものが一つだけでも良い。しかしこれは極端だと思うので＃３案が妥当でしょう。

回答No.3

agricap

ANo.1 の案に類似ですが、時給テーブルを、
開始日、終了日
を加えることをおすすめします。
開始日と終了日を両方もった方が、処理がシンプルになり、また高速に処理できると思います。

「毎日各従業員ごとに時給を入力するのは間違いのもとになると思うので避けたい」
ということですが、一理あります。新規に入力するときも間違いやすくなりますし、たとえば、一度入力してある日付の時給がまちがっていたことに後で気づいた場合、複数日分の時給を修正しなければならず、この作業もまた間違いやすくなりそうです。一方で、日時テーブルで時給をもっていなければ、時給テーブルの１レコードを修正すれば終わるかもしれません。
逆に、各従業員の日給や月給を高速に計算したい、という場合は、ANo.2の案は有力です。
もしくは、日時テーブルに日給をもたせる手も考えられます。

一般には、高速性を重視すると、間違いやすさ、とか整合性、とかテーブル容量は悪い方向になります。
データベース設計の一般的な手法として、ある程度まで正規化してから高速性を考慮して正規形を崩していく、というものがあります。

話は変わって、時給の履歴を管理したい、という目的がある場合は、時給テーブルに開始日、終了日を持つべきでしょう。

このあたりがデータベース設計のむずかしいところで、何を重視するかによって最適解は大きく変わってきます。

補足

>ANo.1 の案に類似ですが、時給テーブルを、
>開始日、終了日
>を加えることをおすすめします。
とはどのように行うのでしょうか。
時給テーブルは従業員番号、従業員名、時給です。ひとりの従業員に複数の時給を持たせることはできないのですか。

回答No.2

noname#22222

時給テーブルの時給はあくあまでも基準時給と考えるという手もあります。
となると、日時テーブルにも列＜時給＞が必要ということになります。
日時テーブルの列＜時給＞の既定値＝時給テーブル＜時給＞という関係です。
日時テーブルの列＜時給＞が既定値から変更された場合、

「時給テーブルの時給も更新しますか？」

とやればと思います。

回答No.1

chukenkenkou

時給テーブルに、（有効）日付を追加してはいかがでしょうか？

最新の時給の日付は「9999/12/31」とします。
2006年5月26日に時給が800円→900円と変更となったなら、
800円、2006/5/26
900円、9999/12/31
のように登録します。

給与計算時は、日付テーブルの日付以上で一番小さい日付の時給を使います。

補足

>時給テーブルに、（有効）日付を追加してはいかがでしょうか？
とはどのように行うのでしょうか。
時給テーブルは従業員番号、従業員名、時給です。ひとりの従業員に複数の時給を持たせることはできないのですか。