Re: systems analysis & database design

Good idea. But if it is really tough to make that change work into your schema, try left joining the 'employee' table. You'll see the job, the employee id (if that's how they're joined) and a null where the name should be.

Bo Gundersen wrote:

> "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
Received on Mon Aug 07 2000 - 00:00:00 CEST