Foreign keys null, empty or always code?

[twgonder]

I've got an adr field for contacts. It will go to a lookup table with an auto ID.
In that lookup table will be records like Mr., Mrs, Miss, etc.
If I don't want an adr for a particular contact, like a young boy (master is a bit dated),
what will work best in the long-run when it comes to reports, forms, code, etc.

I'm wondering what will happen to queries should adr be null or "" for a few values when there isn't an associated record in the lookup table.
Allow for a null, enter "" or use a code to ""?

[June7]

I never allow empty string in fields.

How you handle Null depends on what you are doing with field. Are you concatenating?

[CJ_London]

really depends on what you are using it for.

In the case of your example I would have a table with a single field to store Mr, Mrs etc, indexed no duplicates - i.e. the primary key, The number of records will be low so I wouldn't bother with an autonumber PK and the indexing performance hit for using text as a PK will be virtually none existent.

Up to you whether you want to enforce the relationship or not. Probably not required as typically this sort of data is really just to ensure consistency of spelling, main reason for enforcing would be to have the ability to cascade updates to all the child records - and how often are you going to change 'Mr' to something else?. Note you can create child records with a null FK even with referential integrity, but if you do then set the FK allow zls (zero length string - "") to no

If the FK field is being indexed then I would go for null rather than a zls because you can set the index to ignore nulls which should produce a performance gain with a large number of records

either way you probably won't notice any significant performance degradation for a few thousand records

Personally I prefer nulls to a zls if only for consistency across all datatypes

Note: I have changed my moniker from Ajax to CJ_London as that is what I am known as on other forums

[moke123]

Note: I have changed my moniker from Ajax to CJ_London as that is what I am known as on other forums

Now I'm wondering if I ever knew that

Now people who see

will be left wondering, much like the infamous Ridders.

[isladogs]

Now I'm wondering if I ever knew that

Now people who see

will be left wondering, much like the infamous Ridders.

Who was that?

[twgonder]

At this point, I do have a lookup table using auto numbers for the ID. The reason here is that, it's about the same number of bytes, and if someone wants to change all the "Mr." to "Sr" then it's easy to do.
I suggest to users to but in either Mr. or Sr. depending on what the contact wants, not what my app wants.
The same goes for gender now, my gosh, you should see the table I had to create! No less than 60 description records not including "unknown" or "".

As to usage, there's data entry to consider, how joins might not work properly with nulls, and then if you concatenate the Adr with NmF if you have to test for null and "" before just putting the two fields together with a space, which now involves more logic than just =trim(addr: " " NmF) {or whatever it is in VBA} I did 40 years in a business basic, I love how every version has different syntax for such a BASIC language.

[twgonder]

And then I find this:
NULL v. Zero Length String - Microsoft Access (599cd.com)

and yes, the "has no middle name" vs. "I don't know the middle name yet" could make a difference, say if you want to find out the middle name.
But how would you know in table input or in a text box?

And then the last line of his article assured me that I want to do this correctly from the start:
Also, Access will index NULL values and ZLS values differently, which is a whole different topic of discussion.

[moke123]

Who was that?

Just some guy who pissed off the mods a long time ago and got banned. No one you'd know Mr. Ridd(ers)ington.

[moke123]

As to usage, there's data entry to consider, how joins might not work properly with nulls, and then if you concatenate the Adr with NmF if you have to test for null and "" before just putting the two fields together with a space, which now involves more logic than just =trim(addr: " " NmF) {or whatever it is in VBA} I did 40 years in a business basic, I love how every version has different syntax for such a BASIC language.

In vba you can use the law of propagating nulls. Anything + null is null.

for a full name you can use

(Prefix + " ") & (Firstname + " ") & (MiddleName + " ") & (LastName) & (" "+ Suffix)

There will be no extra spaces in the concatenation if any of the fields are null.
You can do the same thing with addresses.

[twgonder]

Is there a way, when looking at a table datasheet view, to tell all the cells that have a zls and not a null?

[isladogs]

Just some guy who pissed off the mods a long time ago and got banned. No one you'd know Mr. Ridd(ers)ington.

Must have been in a previous life!

[CJ_London]

Is there a way, when looking at a table datasheet view, to tell all the cells that have a zls and not a null?

just looking at table datasheet view, no. In a datasheet form you can use conditional formatting to set the backcolor to something else if null or zls, whichever your prefer)

[Micron]

Is there a way, when looking at a table datasheet view, to tell all the cells that have a zls and not a null?

If you're poking around tables in the normal course of using the db, that's generally not a great idea. In the very least, it offers little in the way of revealing/examining/filtering etc. your data. A query is slightly a better choice and a form is better still for normal use. In this case, a query would spot zls right away if you just want to find any that exist. Criteria would be =""

[CJ_London]

You can set the format property for each field to show something

For numbers it would be ;;;”null”
For text @;”null”