Peter J. Boettcher — Wed, 18 Sep 2002 01:52:08 +0000

I wouldn't allow zero-length strings even for middle name. I would put logic in my handler that would only save the middle name if it's trimmed length was equal to 1 or greater.

You don't have to use nulls as your default value. If you want to use 0 as the default value for a numeric field that would be ok as long as you modified your code to recognize the 0 then be sure not to save nulls because then you would have to check for both.

Your queries will be much simpler when all you have to do is check for null and not for a default value and/or the length of the string.

akohl — Tue, 17 Sep 2002 17:28:34 +0000

So how about the following as a field properties strategy;

fields         required         allow zl strings
------         ---------        ----------------  
nameFirst        yes                no
nameMiddle       no                 yes 
nameLast         yes                no   
cutomerGroupID   no                   
userPassword     no                 no

Fields like first name and last name must have proper string values in order for the listing to make sense. So we don't want to allow any nulls or zero length strings

Middle name can have a null or a zero length string. This way I don't have to worry when I have a web page form which allows the customer to submit a field with middle name input. My db update script won't have to handle that field in any particular way. Whether I pass over it when request.form("txtNameMiddle") equals an empty string so that a value of null is assigned or allow its value to be written to the field in this case resulting in a zero string the db will not return an error.

cutomerGroupID should be able to contain nulls or a numerical value and its default value should be null so that we can query for customers who do not belong to any group with a WHERE IS NULL clause.

userPassword is like customerGroupID except its a text field. We want to allow nulls because not every customer will have a password. Most will use email as password, and we will want to query for customers without passwords. So if the web form brings us an empty string for this field, I want to enforce a scripting policy of passing up this db field rather than assigning it the zero length string.

Am I correct here in assuming that for ease of query purposes whenever I have a field where lack of value will be significant, I should use null as opposed to other default values such as 0 for numerical fields and zero length strings for string fields?

Please comment on all my assumptions here if there are mistakes, not just the question.

Peter J. Boettcher — Mon, 16 Sep 2002 01:34:57 +0000

Yep, that sounds correct.

akohl — Sun, 15 Sep 2002 19:35:57 +0000

Yes, you're right about that. I just looked it up in Access Database Design and Programming by Steven Roman.

So if I choose no for the "required" property and no for the "allow zero length string" then I can place a value of null in the field but not a string value of "".

then I can filter for principal addresses in this example above with WHERE parentAddressID IS NULL.

This make sense?

Peter J. Boettcher — Sat, 14 Sep 2002 07:06:46 +0000

First off, NULL isn't the same as an empty string. Even though you might not allow empty strings you could still insert nulls.

I wouldn't allow any empty strings and just leave the default value as (or insert) NULL.

I'm not 100% sure, but I think by marking the field as "Required" will force you to enter a value (NULLs won't be accepted)

akohl — Fri, 13 Sep 2002 12:00:34 +0000

Now I am really confused. If I choose "no" for "allow empty string", that means that null values are also not allowed, or so I understood from your first post. But now you are saying that if I choose "no", I will be able to test for nulls and very in one step if there is or is not a value in the field.

But if I did not allow nulls, a listing without data in that field would never be written to the table in the first place.

I thought that I would do the following;

users
-----
userID
NameFirst
NameLastphone

addresses
--------
addressID
street
city
state
zip
parentAddressID

Users that have more than one address, would have a primary address, the one he wrote on his account registtation, and secondary addresses, which could be used a alternative ship to addresses.

The listings where parentAddressID are null, are primary addresses. So should I allow empty strings in that field so that I can place a null value there?

Another thing I'm confused about is the "required field" choice. If I say yes, what's the difference between that and not allowing empty strings and nulls?

Peter J. Boettcher — Thu, 12 Sep 2002 13:26:18 +0000

Although this sounds simple, its a very tricky part of database design. Whether to use NULLS or allow empty strings. You definitely do not want to allow both since that makes your queries much more complicated.

My preference is to not allow empty strings. That way if something is NOT NULL then you know for sure that there is data in it, without having to also check for the string length.

akohl — Thu, 12 Sep 2002 12:00:20 +0000

Actually it did help a little. I thought it meant that the field cannot have an empty srting. But it can have a null value.

So if I want to filter a query, "WHERE field1 IS NOT NULL", then I have to choose, "allow empty string", right?

Mark Hensler — Thu, 12 Sep 2002 06:39:41 +0000

It just means that the field can empty, null, zero length, etc. As opposed to not empty, not null, and not zero length.

The ramification is just that you may have a field with nothing in it.

I'm sure that wasn't any help, sorry.