----- Original Message ----- From: "Justin French" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Sunday, August 08, 2004 11:13 PM Subject: NOT NULL
> Can someone give me a quick/clear explanation about why you would > create a table with columns of NULL / NOT NULL? > > I clicked around the MySQL manual last night I *think* I know what's > what, but it'd be great to read one clear paragraph that summarises it > all. > This is the main part of a reply I gave (privately) to the same question a few days ago. ---------------------------------------------------------------------------- ---------------------------------- The best examples I've ever heard for distinguishing between nulls and defined values are: 1. Given a column called Termination_Date in an Employee table, what do you want to store there when the row is created, which is presumably when the employee is hired? Putting a defined value there would usually be a case of putting something factually inaccurate there: after all, you don't know if this new employee will quit before lunch or stay until he/she is 80 and drops dead at his/her desk. I've always understood nulls to mean "Unknown" or "Not applicable" so putting a null in the termination date seems ideal: I don't know when this person is going to leave the company so I simply store a null there to reflect that. Of course, there are some cases where you *do* know the termination date, such as when hiring a contractor on a 3 month contract. On the other hand, the contractor might get fired before the contract ends or may get renewed after the contract is over or even be put into fulltime status so even then the Termination_Date may be a moving target. 2. Given a column containing test scores for students, how would you contrast between the case where a student got every question wrong and the case where the student didn't write the exam for some reason, like illness? And what would you want the average of that column to be? Normally, I would store a zero only if the student actually got every answer wrong and I'd store a null if they didn't write the exam for some reason. In DB2, the AVG() function ignores nulls so that only non-null values are considered in calculating the average. I believe that MySQL works the same way but you should check to be sure. This is exactly the right way to do things, in my view. Otherwise, if you store zero when the student doesn't write the test, you can no longer distinguish between students who failed to write the exam and students who got every answer wrong. Personally, I'd like to be able to distinguish between those cases: students who missed the exam might need to be investigated to see if they were really ill or were just playing hookey; the students who got every answer wrong might need to be assigned a tutor. Also, if you gave students who didn't write the exam a score of 0, their scores would skew the class average down when, in my view, their grades shouldn't be counted in the average. I'd be a little surprised if you had these exact situations in your system but you might have some analagous ones. I'd give that some careful thought if I were you. Those are my two cents worth. Others may have additional points that didn't come to me as I wrote this note. ---------------------------------------------------------------------------- ----------------------------------- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
