Null or Not Null? 1

[junaid6t2]

Hello Everybody,

I am currently designing a database for my website (a MySql database). This concept is new to me, I am having difficulty in understanding which fields should be null and which should be "not null". Does anybody know of any good articles on the net on this subject? I will greatly appreciate it.

Regards,
Junaid.

 

[AHinMaine]

You may be overthinking it...

NULL fields are simply fields that are allowed to be blank. If you want to specifically require that a field have a value and never be allowed to be blank, like a zipcode perhaps, then you would set it to not null. --
Andy

http://www.nachoz.com

 

[r937]

andy, no offence, but you are underthinking it

NULL and blank are not the same

blank and empty string are not the same

empty string and NULL are not the same

NULL and NULL are not the same!!

junaid, check this article --
SQL: Defining and Visualizing NULLs

http://www.reviewnet.net/newsletter/articles/a3_feb02.htm

rudy

http://rudy.ca/

 

[junaid6t2]

I have read the article, it makes more sense now. But I still don't see much use of the null column when adding records to the tables. If the purpose is to notify the user of whether a field is mandatory or not, than I can do that in the java servlet in which I will embed the mysql statements. There seems little point is leaving the database to do these checks (i.e. whether a field is "not null" and therefore mandatory) and then dealing with them. I would rather deal with it at the time of data entry and then add the data to the database in the correct format.

However, it seems much better to have null in a field rather than an "unknown value"; makes searching neater and just makes the tables look a little bit neater and complete too, in my opinion.

Unless ofcourse I am still missing the point here...

Thanks for the help guys, its appreciated.

Junaid.

 

[r937]

the reason you want the database to disallow nulls (when this is appropriate) is so that the data is protected against erroneous input -- no offence, but the java servlet might be coded incorrectly, or the input might be coming from somewhere else, like a gui frontend that builds raw sql

you should never leave data integrity up to the inputters and programmers

 

[junaid6t2]

I'll take your point onboard.

What I am intending to do is to verify data at the point of entry as well as using the null columns to make sure that erroneous input is avoided.

No doubt that database will provide better protection against erroneous input, but on the other hand, the program dealing with the database can provide a user friendly interface (error messages etc) to users who are not interested what's happening with the data at the database level.

I think I'm gonna use a combination of both techniques.

Regards,
Junaid

 

[r937]

doing both is the best strategy

it's like javascript -- yes, you want to provide javascript editing of form fields, to make the experience more pleasant for users, but you also must put the edit routines into the database, to ensure data integrity

the question of null or not null is rather trivial in this context, a better example would be an order without a customer, an invalid date, a 2 in a field which is supposed to contain only 0s and 1s, etc.

the more enforcement that you can define in the database, the better your data integrity

it's nice when you can do it declaratively, e.g. unique constraints, foreign keys, domains, etc., and trickier when you have to do it with triggers

too bad mysql is so weak in declarative integrity, and totally AWOL in triggers -- they are "looking at triggers" for some time around release 5


rudy