To Zero or not to Zero

[Michael Conroy]

This is more of a design question about how data should be stored in a table.
While importing information from Excel, some records in the same field have
zeros and others don't, meaning they are empty. All the fields in question
are numeric. So I am looking at an update query to clean up the table and I
have two choices. I can enter a null value if it equals zero or a zero value
if it equals null. The zero value will make formulas easier, meaning the nz
won't be needed, but the size of the table will increase (OK, maybe not that
much). On the other hand, the fields are like credits and debits, for each
record you will get one number and the other will always be nothing, so the
zero value doesn't really mean anything to me. So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

As always, thanks in advance for any help

 

[Jerry Whittle]

Null means that you don't know. It's like eye color. You assume that people
have eyes, but you don't know everyone's eye color.

0 means nothing. I own 0 aircraft.

Therefore if 0 makes sense in your data (0 credit or debit), I would go with
that especially for the reasons that you gave such as not dealing with NZ and
other null pitfalls.

 

[Jeff Boyce]

Michael

Are you saying that your table structure has one field for Debits and one
field for Credits? If so, you're pretty much guaranteeing that one will be
unused in each transaction row, right?

A couple thoughts ...

First, if you don't have a value, use a null. But a "0" IS a meaningful
value ... it means "I don't have any", not "I don't know" (i.e., Null).

Second, rather than force that kind of double-injury bookkeeping approach on
Access, what about the idea of a table structure like:

tblTransaction
TransactionID
TransactionAmount
DrCr (use this field to indicate whether the amount is a debit or
credit)

(yes, I know, this is grossly over simplified, but what about the approach
of using a single field for the amount instead of two?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Michael Conroy]

Jerry,
The knowing/not knowing part was key. In my case if the transaction has a
credit then I know the debit was zero for that record and versa visa.
Thanks for clearing it up.

 

[John W. Vinson]

So my question is this, what
question should I be asking about the data to determine whether I store a
zero or null value in a numeric field?

I'd ask "Does a blank field have a different interpretation than a zero"? In
some cases it may not - a credit is a good example, where a zero default (and
nulls being forbidden) makes sense. In other situations they might indeed be
different - "How many birds are on my lawn" might be zero (I looked and there
weren't any) or it might be null (I haven't looked and don't have any idea how
many).

It's got to be decided on the basis of the real-life situation.

 

[Michael Conroy]

Gentlemen,
I have read all the responses and I thank you for the input. John,
normalizing the data would eliminate null values but my source file is a
little more complicated. I just found out some airports use the GrossIn fuel
amount, some use the NetIn and both fields can have a value so it is not
exactly like a credit debit. I could still normalize it and add a qualifier
that labels it Gross or Net, but I am somewhat leary of modifying source
information if I can use a query to grab what I want. So I will add this
gross/net "switch" to the airport table and use that to grab the correct
column from the fuel table.

Additionally, I just wanted to add that time is a factor in this discussion.
As I mentioned in the original post I am importing a spreadsheet from another
company that tells us how much fuel is pumped into each plane. I realize I
failed to mention that the data is historical and will never be altered. So
seeing a null value does not mean that they have not pumped the fuel yet, it
means they just never put a zero in the spreadsheet cell. Since I am
normalizing other fields before the data gets added to the main table, in
this case, I feel comfortable adding zeros to their source information to
make my life easier down the road.

So with dynamic data, a zero means the tank is empty, null means you did not
look yet. Thanks again and sorry for the late reply.