[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Tue, 2007-02-27 at 11:39 +0000, Martin Garton wrote:
> On Tue, 2007-02-27 at 11:21 +0000, Duncan John Fyfe wrote:
> 
> > 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.
> 
> Fair point.  For this simple example I was assuming the only reason a
> seat would not be available was because it was booked.  How would you
> handle this case?  With an additional "broken seats" table?
> 

I would implement this as multiple tables.  The point I was making was
that using NULL as was suggested is a convenience which gets you into
trouble.  As soon as you introduce another state (Unavailable) you are
back to square one either having to break third normal form by adding a
new column (booked_by will depend on the original primary key and the
new column) or by adding a new table which you were hoping to avoid in
the first place.  NULLs can be convenient but not if they lure you into
making bad design decisions.

If you want to store 'booked' and 'booked_by' (and maybe unavailable) in
a single column then you must recognise you are creating a compound data
type (like date) and you must add appropriate functions and operators to
the database to allow it to be manipulated without the user needing to
know about the internal storage of the type.  The database can store
values of this new type using special values eg  { 'not booked' ,
{ Valid names } }  but access to the data must not depend on knowing
about these. 

That is of course if you are a database purist.  Much code has and is
being written (eg. http://www.catalystframework.org/) which abstracts
SQL queries and moves the 'leg work' of the database into the
application leaving the database to store serialised objects or
something similar.  Most of this is happening because it means the
application will work with any underlying database which implements a
certain subset of SQL92 rather than having to implement compound data
types for every conceivable database (different API, different language
bindings , ...).    This approach does carry a whole host of issues but
at least they and the application are portable between databases :) and
I can understand why this is attractive to application developers.
 
> > 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.
> 
> Do you mean for columns where NULL has no meaning that you have ascribed
> to it?  I don't yet see what alternative prevents coding errors.

It doesn't prevent them but it helps catch (insert a NULL in NOT NULL
column) them at run time.  If NULL is (unnecessarily) permissible then
your database cannot help catch implicit undef <=> NULLs caused by
coding errors.  

Have fun,
Duncan