List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 14 2006 7:16pm
Subject:Re: Accountability with MySQL
View as plain text  
> > >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

Thread
Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLSGreen14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLRhino15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
      • Re: Accountability with MySQLStephen Cook16 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLMark Leith16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
      • Re: Accountability with MySQLAlec.Cawley16 Mar
        • Re: Accountability with MySQLMartijn Tonies16 Mar
          • Re: Accountability with MySQLAlec.Cawley16 Mar
          • Re: Accountability with MySQLJames Harvard16 Mar
          • RE: Accountability with MySQLGordon16 Mar
            • On MySQL Views (Re: Accountability with MySQL)Martijn Tonies16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
    • Coalesce (was: Re: Accountability with MySQL)Martijn Tonies16 Mar
    • True Propositions (was Re: Accountability with MySQL)Martijn Tonies16 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLSGreen14 Mar
      • Re: Accountability with MySQLMartijn Tonies14 Mar
  • Re: Accountability with MySQLRhino14 Mar
  • Re: Accountability with MySQLRhino14 Mar
Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLRhino15 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar