thanks, hooha, that is exactly what i meant
jofa, i know what you're suggesting, but that sort of semantic clarity often introduces needless complexity
i mean, consider your extra table to store employee events
if the president comes in to my cubicle and says "hey rudy, you got an employee database, right? how many employees did we have on january 1?", i would have to do a left outer join from the employee table to the employee event table, use a filter to look for the "terminated" event, be able to count the correct number of rows for all employees, whether the employee table matched a row in the employee events table or not, and boy oh boy i had better know whether to put the date check for january 1 into the ON conditon of the left outer join or into the WHERE clause, and i guess i also have to know under what circumstances COUNT(*) is going to work correctly or not...
... versus the following:
where date_terminated is not null
or date_terminated > '2002-01-01'
i fully understand your suggestion, but if i've learned anything in over twenty-five years of database design, it's knowing when to stop
edited? yeah, i had < when i shoulda had >
but at least i caught it within 2 minutes