× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2008

Re: Interesting question and debate on ddl tables with datefieldsthat will not always have a value

Bad example...

You're trying to use NULL where it's not appropriate. For a "Loan
Balance" column, NULL would indicate that you don't know if the
student has a loan, which is probably not the case. Instead, you'd
use a zero to indicate that the student doesn't have a loan.

My experience, the use of NULL is a good idea. Provided you use it
only where it is needed or where it provides for some benefit. Which
means that for the given application there is a difference between
NULL and blank/empty/zero field. Most of the time, a blank/zero
default is all you need.

In particular, I find date fields to be very good candidates. Way
back, we'd have used 000000 or 999999 (this was before y2k right :)
Which are not valid dates. So it was easy to tell if a particular
date field didn't have a value. With actual date fields, the DB won't
let you stick in an invalid date, so it's best to use NULL to indicate
that the field doesn't have a value.

Lets consider a column, "Emergency Contact". Would it really matter
to most applications which might contain such a field to know rather
the field is:
-- NULL: the information hasn't gotten the information from me
-- empty/blank: I'm a total loner with no friends or family.

Even in the event that the application really needs to know the
difference, a closer look at the requirements might show that it makes
more sense to have an additional field provide a way to know if/when
the contact information was updated. For instance, a nullable
"Contact Info Last Updated" date field.


Charles Wilt






On Thu, Oct 16, 2008 at 11:37 AM, Mike Cunningham <mcunning@xxxxxxx> wrote:
Going back to the example of AVG() function. Using null fields could also end up giving the end query user the wrong results. If we have a table of students and one of the columns was the total amount of loans each student has taken out and someone wanted to know the average amount a debt for all students the average would be the total of all the loans divided by all students, not the total loans divided by those students who have loans. If that column was null capable and a student never had a loan the column would be null and the AVG() function would ignore the null rows and not return the desired result. Nulls can be good, nulls can be bad. It depends on the column and it's intended use.



"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/16/2008 10:52 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Interesting question and debate on ddl tables with datefieldsthat will
not always have a value






Yes, that's precisely the point... It's a technology choice, much like
deciding whether to use free-form or not... One approach is not
necessarily better than the other, and without a compelling reason to do
so, I prefer to use the techniques and application designs that are
time-tested and well understood in the midrange community. Please note
that this does not mean that I avoid the adoption of new technology.
Quite the opposite, in fact... However, with null support, I do not see
any ROI.

Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: Thursday, October 16, 2008 7:28 AM
To: Midrange Systems Technical Discussion
Subject: RE: Interesting question and debate on ddl tables with
datefieldsthat will not always have a value

Eric,

I've been listening to this discussion since its inception, and I have
the same question as you. Starting out on a System/3 and moving through
the /34 and /36 before moving to an iSeries, there was no such thing as
a null-capable field value. When I started coming across them in the
literature, I could only say that "Gee, we can handle that with blanks
and zeros."

But one still has to implement code that "knows" that a *Zeros TermDate
means that the employee is still employed - or whatever it's supposed to
mean. But whether I test for Null or *Zeros is still a test, isn't it?

Jerry C. Adams
IBM System i Programmer/Analyst
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Wednesday, October 15, 2008 2:24 PM
To: Midrange Systems Technical Discussion
Subject: RE: Interesting question and debate on ddl tables with date
fieldsthat will not always have a value

Yes, I understand...

IMO, null capable fields offer little advantage in a well designed
application. It is simply a technology choice, and like most of these,
its use needs to be applicable to the problem being solved.

So, for me, the question is "what kinds of problems can be solved with
null capable fields?"

I have a hard time with this, since I grew up on IBM midrange... Most
of these problems, to me, are easily solved with status fields and/or
timestamps. My business logic can easily handle pseudo-null logic,
simply by testing the field value. If it still contains the initial
default value, then treat it like a null.... This technique is
understood well in the midrange world.

Just to be fair, I *am* certain that there are circumstances where null
is probably the best choice. I'm just not sure what those circumstances
might be... <g>

Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug Palme
Sent: Wednesday, October 15, 2008 1:27 PM
To: Midrange Systems Technical Discussion
Subject: RE: Interesting question and debate on ddl tables with date
fieldsthat will not always have a value

That is precisely what this exercise is all about Eric, the entire
department is involved in helping determine what our standards should
and
will be.

From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>

To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxx>
Date: 10/15/2008 12:09 PM

Subject: RE: Interesting question and debate on ddl tableswith

datefieldsthatwill not always have a value



----------------------------------------------------------------------

Doug,

No your example is fine, it's just that null does not necessarily
simplify ANYTHING in regard to business application logic. Whatever
one
developer may do with null, another may do with control and status
fields. There is not a right way or wrong way here. Shop standards
should dictate what approach is to be used in your environment.

Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug Palme
Sent: Wednesday, October 15, 2008 11:19 AM
To: Midrange Systems Technical Discussion
Subject: RE: Interesting question and debate on ddl tableswith
datefieldsthatwill not always have a value

Apparently I used a horrible example.......that is not a file that
exists,
I was making it an example for the basis of my question....

From: "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>

To: "'Midrange Systems Technical Discussion'"

<midrange-l@xxxxxxxxxxxx>

Date: 10/15/2008 11:04 AM

Subject: RE: Interesting question and debate on ddl tables with

datefieldsthatwill not always have a value


----------------------------------------------------------------------

A proper system would have a rehire date, as well as reason codes
to
describe the termination. CYA, you know?

Paul Nelson
Office 512-392-2577
Cell 708-670-6978
nelsonp@xxxxxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
lgoodbar@xxxxxxxxxxxxxx
Sent: Wednesday, October 15, 2008 10:14 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Interesting question and debate on ddl tables with
datefieldsthatwill not always have a value

A good employee tracking system should have an employment status
flag.
"Obviously", one would check that flag rather than a null
termination
date.

More relevant: what if the employee is rehired? Should the
termination
date be reset to null? The employee was actually terminated (or
perhaps
laid off) in the past, and setting a null value loses historical
data. A
decent system will have an employment history table; but most I've
seen
have standalone hire and term date fields as well.

Loyd Goodbar
Business Systems
BorgWarner Shared Services
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Tuesday, October 14, 2008 5:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: Interesting question and debate on ddl tables with
date
fieldsthatwill not always have a value

Walden H. Leverich wrote:
> Use nulls, that's what they're for!
>
I'm not picking on you specifically, Walden, because many people
have
contributed to this thread, but yours is the most on-point
statement.

Anyway, not everyone agrees with your blanket assessment of nulls -
including folks like Chris Date and even Dr. Codd himself. A null
in
a
value (in this case, the termination date) could mean lots of
things:
it

could mean that the employee is still working for us (meaning there
is
no termination date). But it could just as easily mean we don't
know
what the termination date was, that the termination date we have is
invalid, or that the termination date hasn't been entered yet.

No, just about every database scientist whom I've read would insist
on a

flag that indicates the status of the employee rather than relying
on
the NULL value to give you any more information than the fact that
you
don't have a termination date.

I won't belabor the point. I just want to make sure that the
notion
of
databases that is currently in vogue today often differs
dramatically
from the folks who actually came up with the concept. That's not
to
say

that there is One True Database Design; Codd and Date actually
disagreed

on nulls. But neither one agreed with the SQL approach.

So anyway, while the general assessment on this list of the use of
nulls

is probably the most common one, that doesn't make it right. And
it
certainly doesn't mean that RPG programmers who don't like nulls
are
some sort of Luddites; indeed, it just means that they choose not
to
use

a specific technique - one that has arguably been misused or at
least
overused by an entire generation of SQL programmers.

Joe

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This transmission may contain information that is privileged,
confidential
and/or exempt from disclosure under applicable law. If you are not
the
intended recipient, you are hereby notified that any disclosure,
copying,
distribution, or use of the information contained herein (including
any
reliance thereon) is STRICTLY PROHIBITED. If you received this
transmission in error, please immediately contact the sender and
destroy
the material in its entirety, whether in electronic or hard copy
format.
Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a
moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This transmission may contain information that is privileged,
confidential
and/or exempt from disclosure under applicable law. If you are not
the
intended recipient, you are hereby notified that any disclosure,
copying,
distribution, or use of the information contained herein (including
any
reliance thereon) is STRICTLY PROHIBITED. If you received this
transmission in error, please immediately contact the sender and
destroy
the material in its entirety, whether in electronic or hard copy
format.
Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.