MS SQL Data Warehousing - Table Relation: Question about concept

Author

Table Relation: Question about concept

Klaus Löffelmann

2005-04-22, 9:23 am

Hello,

I'm comparatively inexperienced with SQL-Server DB-Design, so neither do I
know, if this is the right newsgroup to ask this question nor if I developed
a complete screwed up concept - so my apologies in advance!

OK, here is my problem.

The whole database is about calculation of incentive wages for employees
working in a production unit of a big factory.

I have a table with employees. Working times of those employees are stored
in a table called TimeLog.

The incentives wages people get are depending on the cost enter they were
setup on.

It doesn't matter for the question, how the performance of an employee is
calculated, fact is that his or her performance is indicated in %. So if the
performance of an employee over the average of a month is 104%, she gets a
bonus based on a table, which again is related to the cost enter which has
been setup for that employee.

Now: The data of a cost enter, people are working on, can change, as well as
the performance/bonus assignments in the bonus table which is related to the
cost centre. The problem is: If you simply let the user change the bonus
table or the parameters of the cost centre, past data would become changed,
too (since the TimeLog table is related to the cost center), and a cost
centre calculation wouldn't be accurate anymore. So instead of letting a
user simply change the actual data of a row in the cost centre table, I
though it would be good to rather mark the row as "not current" or
"inactive", just for still having it be able to reflect the "history" of
data, and duplicate the original row to a new one and make the desired
changes only in the new row. From now on, the TimeLog table would use the
now cost center row, therefore reflecting the changes, while the old cost
center would still able to be referenced by the old TimeLog rows.

Even more difficult that approach would become, if I take the bonus table
into account. A change in the bonus table would then cause the same thing to
do with the row of the bonus table, *and* would cause to do the same with
the cost center, in addition.

Since other tables, like wage groups, are effected by that, too, I'm
starting wondering, if there is a better, maybe less complex concept for
maintaining such "history data".

It's kind of difficult to explain, since I'm not native to English (but even
in German the explanation wasn't easy...). I hope that somebody understood
my problem, anyway, and I'd really like to know, if I'm on "the right path"
or if there were other possible concepts for solving this problem.

Thanks a lot

- Klaus

Paul Smith

2005-04-23, 3:23 am

Klaus,

Maybe an intersect table between Employee and CC tables would help track
history of cost centre movement.b The table could have EMID,CCID, STARTDATE,
ENDDATE, PK is the first 3 columns. For convenience set the ENDDATE
to some high value (i.e 2999-12-31') instead of NULL when there is no
ENDDATE, tis will help with using the BETWEE DML constructs.

Have a BONUS table containing CCID, %TGE STARTDATE, ANDDATE.

Use the datestamp on the TIMELOG table to help in joining the whole lot
together.

Just a thought

Paul
"Klaus Löffelmann" < fornewsgroups@loeffe
lmann.de> wrote in message
news:elTRT5zRFHA.576@TK2MSFTNGP15.phx.gbl...
> Hello,
>
>
>
> I'm comparatively inexperienced with SQL-Server DB-Design, so neither do I
> know, if this is the right newsgroup to ask this question nor if I
> developed a complete screwed up concept - so my apologies in advance!
>
>
>
> OK, here is my problem.
>
> The whole database is about calculation of incentive wages for employees
> working in a production unit of a big factory.
>
> I have a table with employees. Working times of those employees are stored
> in a table called TimeLog.
>
> The incentives wages people get are depending on the cost enter they were
> setup on.
>
> It doesn't matter for the question, how the performance of an employee is
> calculated, fact is that his or her performance is indicated in %. So if
> the performance of an employee over the average of a month is 104%, she
> gets a bonus based on a table, which again is related to the cost enter
> which has been setup for that employee.
>
>
>
> Now: The data of a cost enter, people are working on, can change, as well
> as the performance/bonus assignments in the bonus table which is related
> to the cost centre. The problem is: If you simply let the user change the
> bonus table or the parameters of the cost centre, past data would become
> changed, too (since the TimeLog table is related to the cost center), and
> a cost centre calculation wouldn't be accurate anymore. So instead of
> letting a user simply change the actual data of a row in the cost centre
> table, I though it would be good to rather mark the row as "not current"
> or "inactive", just for still having it be able to reflect the "history"
> of data, and duplicate the original row to a new one and make the desired
> changes only in the new row. From now on, the TimeLog table would use the
> now cost center row, therefore reflecting the changes, while the old cost
> center would still able to be referenced by the old TimeLog rows.
>
> Even more difficult that approach would become, if I take the bonus table
> into account. A change in the bonus table would then cause the same thing
> to do with the row of the bonus table, *and* would cause to do the same
> with the cost center, in addition.
>
>
>
> Since other tables, like wage groups, are effected by that, too, I'm
> starting wondering, if there is a better, maybe less complex concept for
> maintaining such "history data".
>
>
>
> It's kind of difficult to explain, since I'm not native to English (but
> even in German the explanation wasn't easy...). I hope that somebody
> understood my problem, anyway, and I'd really like to know, if I'm on "the
> right path" or if there were other possible concepts for solving this
> problem.
>
>
>
> Thanks a lot
>
>
>
> - Klaus
>
>

Klaus Löffelmann

2005-04-23, 3:23 am

Hi Paul,

that's an interesting approach, too.
But since I have to deal with probably up to about 10.000 time bookings
(right word?) a day - my biggest customer has 7 subsidiaries with about 400
employees each, and people constantly changing between workgroups - I'm
afraid that the search for data for the right dates will be too slow than
directly indexing them.

But I will use an intersect table anyway, which I think will limit the
"history maintenance" just to one level. I'm going to include the reference
to the bonus list and to the wage group list into the TimeLog table, that
should do the trick.

Sorry, BTW - my German spell checker "corrected" "costcenter" into "cost
enter" which I didn't notice.
(Figures, though... ;-)

Thanks a lot

Klaus

"Paul Smith" < paul@spamno_sagestor
e.com> schrieb im Newsbeitrag
news:uWfghX8RFHA.3144@tk2msftngp13.phx.gbl...
> Klaus,
>
> Maybe an intersect table between Employee and CC tables would help track
> history of cost centre movement.b The table could have EMID,CCID,
> STARTDATE, ENDDATE, PK is the first 3 columns. For convenience set
> the ENDDATE to some high value (i.e 2999-12-31') instead of NULL when
> there is no ENDDATE, tis will help with using the BETWEE DML constructs.
>
> Have a BONUS table containing CCID, %TGE STARTDATE, ANDDATE.
>
> Use the datestamp on the TIMELOG table to help in joining the whole lot
> together.
>
> Just a thought
>
> Paul
> "Klaus Löffelmann" < fornewsgroups@loeffe
lmann.de> wrote in message
> news:elTRT5zRFHA.576@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links