I'm currently working with a DB that has int fields with both nothing and zero (0) and the two different values mean different things. No value means no measurements were taken and 0 means a measurement of zero.
If I use ResultSet.getInt() on this field I get "0" for both cases. Is there a way to read these fields and differentiate between 0 and no value?
Thanks,
-=beeky
Try the wasNull method after reading the value. It should return true if the value was actually a null rather than 0.
Personally there is no place for nulls in a relational database but I'm sure you don't care to hear my opinions on that one so we'll just leave it with the wasNull comment.
> > Personally there is no place for nulls in a
> > relational database but I'm sure you don't care to
> > hear my opinions on that one so we'll just leave
> it
> > with the wasNull comment.
>
> You are saying that nulls should not be used in a
> relational database?
Yes that is indeed what I am saying.
> > > Personally there is no place for nulls in a
> > > relational database but I'm sure you don't care to
> > > hear my opinions on that one so we'll just leave it
> > > with the wasNull comment.
> >
> > You are saying that nulls should not be used in a
> > relational database?
>
> Yes that is indeed what I am saying.
So exactly how would you design the database for the following business case?
The customer wants a management tracking system that has the following features.
1. Provides a phone list to be printed which is alphabetized.
2. The business specifically notes that "Cher" is a customer as well as "John Smith" and "Edward James Symthe, III".
3. The business accepts that "Cher" will be used as the last name for sorting.
> > > > Personally there is no place for nulls in a
> > > > relational database but I'm sure you don't care
> to
> > > > hear my opinions on that one so we'll just
> leave it
> > > > with the wasNull comment.
> > >
> > > You are saying that nulls should not be used in
> a
> > > relational database?
> >
> > Yes that is indeed what I am saying.
>
> So exactly how would you design the database for the
> following business case?
>
> The customer wants a management tracking system that
> has the following features.
> 1. Provides a phone list to be printed which is
> alphabetized.
> 2. The business specifically notes that "Cher" is a
> customer as well as "John Smith" and "Edward James
> Symthe, III".
> 3. The business accepts that "Cher" will be used as
> the last name for sorting.
I don't see the need for the null here. I suppose you are going to have two fields one for first name and one for last name? And perhaps a third for initial.
Well so any of those fields can be zero length. What's the problem with that?
Perhaps if I explain what my fundamental problem with nulls is. A nullable column indicates that you have a relationship that is not broken out.
From a design perspective that means you have done something wrong.
if you want to use a nullable column instead of a seperate table for performance reasons well then I am not going to stop you. Although I am not convinced of the need because if you have a column in a table where you have alot of nulls that is slowing things down too.
At any rate when I see a nullable column I see a denormalized table and that's my issue. Because null isn't zero-length. Null is null. Means it doesn't exist. Means it is optional. Means it should be represented by a relationship in your database.
> > > Personally there is no place for nulls in a
> > > relational database but I'm sure you don't care
> to
> > > hear my opinions on that one so we'll just leave
> > it
> > > with the wasNull comment.
> >
> > You are saying that nulls should not be used in a
> > relational database?
>
> Yes that is indeed what I am saying.
I've seen many of your posts and I have great respect for your knowledge but I must respectfully disagree with you on your statement regarding "no place for nulls" in a relational structure.
I don't know your background, but I'm curious if you have worked with relational databases outside of the Java language? Here is why I抦 asking:
Java has a ridiculous approach to handling nulls, it almost seems like they forgot about null in the first cut of the JDBC API and then added in the wasNull() as an after thought. If Java was the only language I used (and I wasn抰 a DBA) I can imagine why you might be saying that null doesn抰 belong (because in Java it抯 a pain in the ***). Coding for null in Java is just physically ugly and the API can be a little confusing and always frustrating.
Having used other languages where dealing with null wasn't so odd and having designed a variety of large (first gigabyte then terabyte size) databases I cannot imagine null not being part of those relational database structures.
Could this be part of the difference in our perceptions (Java抯 API)? If not, are there are reasons that you are so certain on this point? I抦 not looking to be argumentative; I抦 hoping to learn something from your experiences.
In my experience the storage implications of not having nullable columns would be enormous. Conceptually the statistician would be lost without the concept of null (missing value) because many of the standard statistical calculations wouldn抰 even work. From a design perspective when you are forced to move away from 3rd normal form for performance reasons (and it happens often with large implementations) it can become critically important to be able to differentiate between a column with a value and one with a missing value.
> Perhaps if I explain what my fundamental problem with
> nulls is. A nullable column indicates that you have a
> relationship that is not broken out.
I already guessed what you thought the problem was.
>
> From a design perspective that means you have done
> something wrong.
>
Not at all. As an analogy the fact that every single item in my application is not an object doesn't mean that my design is wrong or that isn't OO.
The goal of a design is to meet the business needs. Those needs can vary but most businesses would not accept the burden of performance and time (cost) that would be imposed by making every single application meet all idealized metrices of what an "ideal" application should be.
> if you want to use a nullable column instead of a
> seperate table for performance reasons well then I am
> not going to stop you. Although I am not convinced of
> the need because if you have a column in a table
> where you have alot of nulls that is slowing things
> down too.
>
You didn't provide a design. For the scenario that I gave you are you really going to add a name table and then specify a first and last name descriminator?
> At any rate when I see a nullable column I see a
> denormalized table and that's my issue. Because null
> isn't zero-length. Null is null. Means it doesn't
> exist. Means it is optional. Means it should be
> represented by a relationship in your database.
And what if the relationship is indeed optional?
I wouldn't say that nulls ALWAYS indicate a lack of normalization. Frequently, maybe, but not always.
Consider an employee database. It might contain the employee's retirement date. For employees that have retired, there's a date in the column. For employees that haven't yet retired, there's null in the column. Personally I wouldn't break that column off into a separate table. And that isn't for performance reasons, either. It just looks weird to have date hired (which is never null) in the main table and date retired in a separate little table of its own.
>
> Well so any of those fields can be zero length.
> What's the problem with that?
I missed that comment - because at least one major database will not let you have a field that has a zero length and which is not null. I am not entirely sure that any database will allow that. If I recall correctly I remember that one ends up putting a single space character in for that case - and that is not a zero length value.
