allow zero length strings in this field

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.

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?

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.

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?

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)

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?

Yep, that sounds correct.

So how about the following as a field properties strategy;

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.

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.