Re: systems analysis & database design

"the fat heffer" <iamthecow_at_hotmail.com> wrote in message news:965637510.5177.0.nnrp-01.9e981bb7_at_news.demon.co.uk...

> 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.

One way of solving your problem, is to avoid physically deleting the records from the employee table, but to mark them deleted. This can be done by adding start_date, end_date columns to the employee table, allowing you to reference past employees. Instead of deleting an employee, you assign SYSDATE to his end_date, thereby logically deleting him. Of course this solution calls for some management when quering current employees, but this can easily be solved by creating a view like:

CREATE VIEW current_employees AS
SELECT * FROM employees WHERE
end_date IS NULL;

The above code assumes that current employees have a NULL end_date, you could also choose a very large date for currently active employees.

Richard T. Snodgrass has written a whole book on how to manage temporal data (as this), it's called "Developing Time-Oriented Database Applications in SQL", it takes a very practically approch to temporal data.

Also my company is developing a tool called tDeveloper to help manage temporal data. A beta download should be available very soon, feel free to write for more information or to be included in our announcement mailing list :)

--
Bo Gundersen
R&D Timechain Technology
http://www.timechain.com