Re: systems analysis & database design

The Start date/End date thing is a good idea. However, when I had to manage a similar problem I simply added a checkbox called "archive" that users could check. (they were more likely to check a box than enter a date correctly)this works exceptionally well because I could then build my forms from a query that eliminated the archived entries, yet I could still build reports from everyone. I.e. staff couldn't see them or accidently use them if they were archived yet I can pull up their data or include their statistics in a report, and it saved me some coding.

In article <T0vj5.16827$MJ5.227077_at_twister.sunsite.auc.dk>,   "Bo Gundersen" <bo_at_timechain.com> 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
>
>

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