Dates and NULLs

[sak]

I have read many of the articles/forum topics on this site and on Database Debunkings about NULL values and the issues involved.

One question I have is dealing with dates and NULLs when the date is not known yet. In an Employees table one of the fields in TerminationDate. This field has a value of NULL if the employee is active. Is this use of NULL an issue? Are there recommend solutions to avoid the NULL? I have thought of using a Default value but am not sure what a good value would be.

Thank you

[alf-admin]

Yes, you bring up an interesting issue. In your case (IMHO) you (or rather, the construction you describe) are mixing two different entities: EMPLOYEE and ENGAGEMENT (or whatever you want to call it). Let me elaborate:

I often see EMPLOYEE entities with a start and end date for engagement. And of course, when the employee is active, end date is NULL. But imagine where you have an employee coming and going? In the case described above, an employee can only be employed once... There should really be another entity that recorded, for a given employee, when he started, and when he ended being an employee, possibly classified by a type entity marking each row as a "Begin" or "End" event for the engagement.

The problem of having a NULL for end date as you describe it, is not a NULL problem: It is a flaw in DB design, not recognizing that there is a one-to-many relationship between an employee and his/her ENGAGEMENT as an EMPLOYEE.. The flaw is more often a result of deliberately over-simplifying the DB design, also known as lazyness

Just as a short remark on your question.

Edit:

Actually, your example is a perfect complementary to one of my main "theses": If your DB design contains attributes that allow NULLs, it is a very good indication of incomplete/flawed DB design.
_________________
All the best,
Alf
Forum Admin
Light travels faster than sound: That's why so many people appear bright until you hear them speak...
www.databasedesign-resource.com
The Database Normalization eBook

[sak]

Thank for your quick response.

[alf-admin]

[sak]

Fortunately my boss understands.

When I started here they used to kick everybody out of the database (Access 97) for 2 hours so they could run Invoices. It turns out the previous programmer, hereby called DM, had constructed the query as "SELECT * FROM qryInvoice WHERE InvID=1234" in order to print invoice 1234. Unfortunately, qryInvoice was returning a record for every single invoice in the system by going to all the worksheets line by line and calculating totals. Everyday the process would get a little slower because there were more worksheets. My boss would ask DM if all invoices were being calculated because the process was so slow but DM would always answer that only 1 invoice was being returned.

I modified the process to only calculate the invoice in question and the invoice process now only takes a few minutes and people are able to continue in the database. I have proven my worth and my boss feels good about having the correct suspicion behind the performance issue.

I may not know everything about how to work with databases but I know that there are preferred practices and I take what time I can to seek out information. Which is why I am here.

[alf-admin]