[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Tue, 2007-02-27 at 09:35 +0000, Martin Garton wrote: 
> On Tue, 2007-02-27 at 09:06 +0000, Duncan John Fyfe wrote:
> 
> > 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'.
> 
> A meaning being ascribed to null may be wrong in relational theory[1],
> and may be unpleasant to some, but certainly works perfectly well in
> many real situations.  In fact solutions like mine often work better in
> practise. One reason is that because by always avoiding nulls at all
> costs can actually make things harder for developers to understand and
> so bugs spring up for different reasons.
> 

Granted NULL can be convenient but it is degenerate in meaning and
dangerous.  Being alluringly convenient in a way that encourages bad
design shortcuts makes it even more dangerous.

Given the nature of the Booked_by column (seat status) and the ease with
which the NULL can be avoided I would avoid it like the plague.  By
avoiding the NULL you can also mark the column as NOT NULL to trivially
catch naughty NULLs.  

If you use NULL to flag 'available' how do you record a seat as
'unavailable and not booked' (eg. seat 10 has been broken) ?  
Here is a new perfectly reasonable 'not booked' state.
To accommodate this in your NULL based scheme you must change the
database (and adding a new column takes you back to having booked_by
depend on something other than the primary key) or add another special
value to indicate 'unavailable' and thereby make the column into the
data type as I have suggested.  

This isn't just about normalisation, this is about the sane encoding of
meaning and the preservation of sanity by not being lured into a false
sense of convenience by misusing NULL.  Take for example perl DBI which
translates an undef (uninitialised)  variables into a NULL in the DB.
This is a very good non-normalisation reason to avoid NULL's like the
plague - any automatic undef <=> NULL mapping hides coding errors and
allows simple errors to ruin your database.

Have fun,
Duncan