> > >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
> >>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.
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
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
> 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
> is 0 for the students who got every question wrong and NULL for the
> 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:
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
> 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
> 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:
TERMINATED_EMPLOYEES (albeit a bit agressive ;) )
Once more: perfectly valid design.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!