[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Mon, 2007-02-26 at 14:52 -0800, Ovid wrote:
> --- martin <martin at stupids.org> wrote:
> 
> > I agree with the normalisation in your example, and also with the use
> > of
> > surrogate keys, but doesn't the problem here come from duplication of
> > information rather than a de-normalised layout?
> > 
> > In other words, by removing the "booked" flag and instead using
> > booked_by == null to indicate no booking, the integrity issue goes
> > away.
> > 
> > As I said, I can't disagree on using 3rd normal form, but it doesn't
> > seem to me to be the main problem in this case.
> 
> I can see your point and many would argue that your's is a reasonable
> solution.  In fact, it's a simpler solution.  However, I'm one of those
> database people in the 'avoid NULLs whenever possible' camp.  Another
> frequent source of serious database errors is allowing NULL fields. 
> I'm not so fanatical that I refuse to design a database where NULLs
> exist, but I do go out of my way to avoid them.
> 
> For those curious as to why, I have a fuller discussion at
> http://use.perl.org/~Ovid/journal/27927.  It's short, but it might take
> a couple of reads to understand how NULLs in a database can lead to
> very wrong answers.  There are a lot of problems we face every day
> because popular databases ignore relational theory and allowing NULLs
> is a symptom of this.
> 
> 

> 
But surely Martin's solution is wrong because it depends on a meaning
being ascribed to NULL.  NULL can arise from conditions other than those
anticipated and when it does you will be unable to distinguish 'not
booked' from 'another condition you did not anticipate'.

To do this in a single column you are in effect creating a new data type
'Booked_by'. The game being how to serialize the values of 'Booked_by'
in a human readable text string.  This is easy when the value 'not
booked' and (names) are mutually exclusive and it is trivial to encode
'not booked' as a text string which will never conflict with a real name
but the main point is you are not abusing NULL to do it.


PS.  perl -le 'print "yes" if (undef) < 4'

compare with 
	perl -le 'print "yes" if (6) < 4'
	perl -le 'print "yes" if ("a") < 4'

I think this is because "(undef)" is being parsed as a list with a
single element (which happens to be undefined) while "(6)" is a
numerical expression in parenthesis which is evaluated first.
Ah, the joys of degenerate syntax.
HTH.

Have fun,
Duncan