To NULL or Not To NULL

[stanl]

Theoretically......

Working on a freeware DB application for non-profits. The issue came up about permitting certain fields to be NULL. Since the data will be manipulated with ADO and use MDAC 2.8 I argued that all fields should have a default value since there might be issues outputting to Excel, Text or other formats using the Jet 4.0 Provider.

My suggestion was rejected and it was argued that NULL's were easier to program and the DB would be smaller. I have come across a few posts about 'bugs' in MDAC 2.8, especially w/text drivers, but have found no official Microsoft acknowledgement or SP release.

Anyone care to weigh in?

TIA
Stan

[Bob Phillips]

Stan,

I am with you. NULLs are a pain in the proverbial. They are a database feature that cause all sorts of problems elsewhere. My applications never have NULLs.

I would have thought the DB size could not be an issue. If your database is so big, anb so many fields could be NULL that it would make a difference suggests there is a DB design issue there.

[stanl]

No, IMHO the DB is designed correctly [par moi]. The issue of 'size' is a management tool to appear important.

Management often assumes NULLS are required in situations such as a totalcontribution field. A list of contacts is called, some pledge 0, so NULL is assumed to differentiate those who pledge 0 from those who have not been contacted. But those who designed the DB know that determination is based on whether or not a record exists for that contact in a related table with dates/times for when contacted and the resolution.

I have heard that in converting from say Access to MySQL there are definite issues with NULLS.

Anyway... thanx for the support Stan

[Bob Phillips]

No, IMHO the DB is designed correctly [par moi]. The issue of 'size' is a management tool to appear important.

I wasn't impugning your database design . I said ... If your database is so big, and if so many fields could be NULL ... as an argument to support your view, as I assumed that there wouldn't be so many nulls.

Another point is that it is poor design (VERY poor design) to try and use one filed to designate identify different things, such as a field that numbers contributions AND determines whether a contribution has been made (which you haven't done anyway, it is in another table).

[stanl]

Not to draw this discussion out unnecessarily... but I generally use ADOX to create and modify tables via code. NULLABLE fields are set with

Jet OLEDB:Allow Zero Length = True

and it appears you can change most attributes 'cept this one so

Jet OLEDB:Allow Zero Length = False

will not work on an exisiting field. AAARRGGGHH! Stan