Re: systems analysis & database design

In article <965637510.5177.0.nnrp-01.9e981bb7_at_news.demon.co.uk>,   "the fat heffer" <iamthecow_at_hotmail.com> wrote:
> hi i have a qestion regarding the design of a data base,
> i am designing a database which has table in it called jobs,
> ths table will shows all the jobs that the company has done and the
 employee
> who has worked on them.
> I also have an employee table which has a record of all the employees
 and
> their wage.
> the jobs table will work out the ost of the job by multiplying the
 time
> spent on the job by the wage.
> I have done this and i think it is quite easy.
> The thing i dont understannd is what happens when an employee leaves,
 as the
> employee will have no entry on the employee table so when looking
 back to
> when they did a job their will be no value for the cost of it.
> Please could some one tell me if there is a standard way to get round
> problems like this.

Have you considered what happens when an employee's wage rate changes? It doesn't seem to account for the fact that, for project work in prior months, and employee may have previously cost less then he/she currently does.

The employee table could join (one to many) to a second table with employee number, start date, end date and wage rate. When an employee is terminated, simply load the termination date to end date. When an employee get a raise, load the date to end date and create a new record with the start date and new increased wage rate.

Patrick McMullen

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 07 2000 - 00:00:00 CEST