> > >Should you have a flag for the status "movement complete" ? I would say > >>yes but instead of a simple checkbox, you could store a date value. That > >>gives you two pieces of information > >> > >>a) if the date is null then the movement is not complete. > >>b) if the date is NOT null then the movement is complete and you know when > >>it finished. > > > > There, Shawn and I disagree :-) > > > > In case of a very simple database, one could use NULL > > as a "flag" or "signal". But in general, I would avoid > > storing NULLs. > > > > Basics of database design: store what you know. > > > > Given that NULLs basically means the absence of a value > > (eg: unknown), you shouldn't be storing NULLs. > > > Nonsense!!
That's a bold statement ... > That's simply wrong. A null means "unknown or not applicable" and is a Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... > perfectly valid value to use in many, many situations. Two classic examples: Let me first state that there's a difference between storing NULLs and handling NULLs in your result. So, let's debunk these two classic examples... > 1. You have a table containing test scores. Some students get every question > on a given test wrong. Some students don't write the test at all, perhaps > because they were sick. In this scenario, I think the correct thing to store > is 0 for the students who got every question wrong and NULL for the students > who didn't write the test. IMO, the better thing to do in this particular case is to NOT store a test result for the students that did not make the test. So, an example table with test scores: TEST_SCORES StudentID Int, TestID Int, Score TinyInt Unsigned Now, students who didn't make the test won't have a record in here. Perfectly valid design AND you avoid storing NULLs. > 2. You have a table containing employee records. One of the columns is > "termination date". What value do you store for a new employee's termination > date? Well, if they are a contractor on a fixed length contract, you could > calculate the date the contract ends; fair enough. If they are a permanent > employee and your area has mandatory retirement, you could calculate the > date they turn 65 (or whatever) and use that. But what if they are a > permanent employee and you don't have mandatory retirement? I would store a > NULL to mean "I don't know right now". Then, if and when they gave notice > that they were leaving, I would change the termination date from NULL to > their last day of work. Why store a date column if you don't know? Why not use: EMPLOYEES EmployeeID int, StartingDate Date, ... TERMINATED_EMPLOYEES (albeit a bit agressive ;) ) EmployeeID TerminationDate Once more: perfectly valid design. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
